Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
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.
1 2 | 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.
Let’s try;
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.
1 | 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.
