There is a query executed via JDBC, for example:

INSERT INTO TableName( id, name, phone) VALUES ('1', 'new_name', '12345');

A record with this key already exists, and it occurs:

 duplicate key value violates unique constraint "TableName_pkey"

How to make a complete rewrite of the entire record, if a record with such a key already exists?

Thought it is possible to use: ON CONFLICT DO UPDATE but in this case it is necessary to specify which fields need to be updated. But you need to do a full rewrite of the string.

  • delete and add again? - Aleksey Muratov
  • And why do you make an insert if you want to make an update? Why do I need to overwrite? - Roman Danilov
  • He does not want to specify the fields for updating, I want to see through the insert, then only with deletion. But the update is better in this case - Aleksey Muratov
  • I will clarify a little. The data comes to me in the form of JSON (from Kafka) and from it is generated insert. When a modified record arrives, in order not to write the code separately for generating the update, it is easier to rewrite the entire record again via insert. - Leonid Dubravsky
  • completely replace the entire entry. - Leonid Dubravsky

2 answers 2

If it’s straight SQL, and we don’t really want to check beforehand the fact of the record (we want to fit everything in one request), then for PostgreSql we use the following construction

 INSERT INTO TableName( id, name, phone) VALUES ('1', 'new_name', '12345') ON CONFLICT (id) DO UPDATE SET name = 'new_name', phone = '12345' WHERE id ='1'

    If you have a known primary key, then you need to do an update, not an insert. It will be right

     UPDATE TableName set name='new_name', phone='12345' where id='1';
    • I have an insert request formed from json. A json object comes, is parsed, and the code forms a request from it, taking into account nested objects (table links) and other things. Accordingly, you need to write another code that will form an update request. So I'm looking for how it is easier to solve. - Leonid Dubravsky
    • @ LeonidDubravsky if you know the key value, then you need to do an update, if not, then insert. New objects are created after key generation, you can do this in the database or use a key generator, but you need to distinguish new objects from old ones. If there are conflicts with the keys, this means that the data is duplicated or they need to be updated. It depends on the method used in the request. - Roman C
    • My key comes with the data, it is not generated when the data is first written. But as I understand it, it’s correct to do a check for availability by key and, if it exists, to generate an update request. If there is no entry by key, generate an insert request. - Leonid Dubravsky
    • The main function of the key generator is uniqueness, that is, two different objects cannot have the same keys. Therefore, the insertion of data with such keys is uncontrolled, the check must be made not only of the key but also of the entire object before updating. - Roman C
    • The essence of all these records is the transfer of data from one database through broker kafka to another database. Records come to me with unique keys. The only thing is that the records sometimes come with updates (the full record also comes to the update), and then they need to be re-saved. - Leonid Dubravsky