Shulou(Shulou.com)05/31 Report--

This article mainly introduces "the method of adding when there are records and updating without records in Postgre SQL database". In the daily operation, it is believed that many people have doubts about the method of adding new records when updating records in Postgre SQL database. The editor consulted all kinds of materials and sorted out simple and useful operation methods. I hope it will be helpful for you to answer the doubt that "if you have a record, update a new method without a record" in Postgre SQL database! Next, please follow the editor to study!

By using the on conflict keyword in PostgreSQL, you can easily implement functions that can be updated or added without:

Create a test table with id as the self-incrementing primary key and cust_id as the user id,name as the user name:

Create table test_cust (id serial primary key, cust_id int, name varchar (20))

Create a unique constraint for the field cust_id:

Create unique index idx_tb_cust_id_unq on test_cust (cust_id)

Add three records to the table:

Insert into test_cust (cust_id,name) values (1,'a'); insert into test_cust (cust_id,name) values (2,'b'); insert into test_cust (cust_id,name) values (3,'c'); select * from test_cust

When you add a record with a cust_id of 3 to the table again, the new record will report an error due to the unique constraint of cust_id:

Insert into test_cust (cust_id,name) values (3,'b')

Use the on conflict statement to update the record with cust_id 3 and change the user's name to e:

Insert into test_cust (cust_id,name) values (3,'e') on conflict (cust_id) do update set name='e'; select * from test_table

If no action is taken when there is a record, and if there is no record, it will be added. This can be achieved as follows:

Insert into test_cust (cust_id,name) values (3,'e') on conflict (cust_id) do nothing

It is important to note that the field cust_id in conflict (cust_id) must be created with unique constraints.

At this point, on the "Postgre SQL database implementation records are updated without records will be added to the method" on the end, I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.