With PostgreSQL, it is very easy to manage the case “update if there is a record, if not, add”. In traditional methods, we first check whether a record with a SELECT statement is in the table, and then run the INSERT or UPDATE statement as the case.
In PostgreSQL, we can resolve this situation with a single INSERT statement.
In addition, we get better performance than the traditional method.
Create a table to see the usage.
CREATE TABLE upserttest AS
SELECT * FROM (VALUES (1,'a'), (2,'b')) AS foo(id, value);
As you can see, we don’t have a primary key in the table.
In order to use INSERT .. ON conflict .., the column in the conflict section must contain a primary key or unique index.
add / update an SQL record with id 1 and see our error:
ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification
Now let’s add our primary key and try again.
ALTER TABLE upserttest ADD PRIMARY KEY (id);
Now we have a primary key. Let’s try again and see the result.
We have seen that the value field of the record with the id 1 is updated to “a2’. Now, with the same command, let’s add a record of id 3, and let’s see the new record.