Summary: this tutorial shows you how to use the PostgreSQL upsert feature to insert or update data if the row that is being inserted already exists in the table.
Introduction to the PostgreSQL upsert
In relational databases, the term upsert is referred to as a merge. The idea is that when you insert a new row into the table, PostgreSQL will update the row if it already exists, otherwise, PostgreSQL inserts the new row. That is why we call the action is upsert (update or insert).
To use the upsert feature in PostgreSQL, you use the INSERT ON CONFLICT statement as follows:
1 2 | INSERT INTO table_name(column_list) VALUES(value_list) ON CONFLICT target action; |
PostgreSQL added the ON CONFLICT target action clause to the INSERT statement to support the upsert feature
The target can be:
-
(column_name)– a column name. -
ON CONSTRAINT constraint_name– where the constraint name could be a name of the UNIQUE constraint. -
WHERE predicate– a WHERE clause with a predicate
The action can be:
-
DO NOTHING– means do nothing if the row already exists in the table. -
DO UPDATE SET column_1 = value_1, .. WHERE condition– update some fields in the table.
Notice that the ON CONFLICT clause is only available from PostgreSQL 9.5. If you are using an earlier version, you will need a workaround to have the upsert feature.
If you are also working with MySQL, you will find that the upsert feature is similar to the insert on duplicate key update statement in MySQL.
PostgreSQL upsert examples
Let create a new table named customers to demonstrate the PostgresQL upsert feature.
1 2 3 4 5 6 | CREATE TABLE customers ( customer_id serial PRIMARY KEY, name VARCHAR UNIQUE, email VARCHAR NOT NULL, active bool NOT NULL DEFAULT TRUE ); |
The customers table consists of four columns: customer_id, name, email, and active. The name column has an associated unique constraint to guarantee that the uniqueness of the customers.
1 2 3 4 5 6 7 8 9 10 11 | #\d customers Table "public.customers" Column | Type | Modifiers -------------+-------------------+----------------------------------------------------------------- customer_id | integer | not null default nextval('customers_customer_id_seq'::regclass) name | character varying | email | character varying | not null active | boolean | not null default true Indexes: "customers_pkey" PRIMARY KEY, btree (customer_id) "customers_name_key" UNIQUE CONSTRAINT, btree (name) |
The following INSERT statement inserts some rows into the customers table.
1 2 3 4 5 6 7 8 9 10 11 | INSERT INTO customers (NAME, email) VALUES ( 'Microsoft', ), ( 'Intel', ); |
1 2 3 4 5 6 7 | #SELECT * FROM customers; customer_id | name | email | active -------------+-----------+-----------------------+-------- 1 | IBM | contact@ibm.com | t 2 | Microsoft | contact@microsoft.com | t 3 | Intel | contact@intel.com | t (3 rows) |
Suppose Microsoft changes the contact email from [email protected] to [email protected], we can update it using the UPDATE statement. However, to demonstrate the upsert feature, we use the following INSERT ON CONFLICT statement:
1 2 3 4 5 6 7 8 | INSERT INTO customers (NAME, email) VALUES ( 'Microsoft', ) ON CONFLICT ON CONSTRAINT customers_name_key DO NOTHING; |
The statement specified that if the customer name exists in the customers table, just ignore it (do nothing).
The following statement is equivalent to the above statement but it uses the name column instead of the unique constraint name as the target of the INSERT statement.
1 2 3 4 5 6 7 8 | INSERT INTO customers (name, email) VALUES ( 'Microsoft', ) ON CONFLICT (name) DO NOTHING; |
Suppose, you want to concatenate the new email with the old email when inserting a customer that already exists, in this case, you use UPDATE clause as the action of the INSERT statement as follows:
1 2 3 4 5 6 7 8 9 10 | INSERT INTO customers (name, email) VALUES ( 'Microsoft', ) ON CONFLICT (name) DO UPDATE SET email = EXCLUDED.email || ';' || customers.email; |
1 2 3 4 5 6 7 | #SELECT * FROM customers; customer_id | name | email | active -------------+-----------+---------------------------------------------+-------- 1 | IBM | contact@ibm.com | t 3 | Intel | contact@intel.com | t 2 | Microsoft | hotline@microsoft.com;contact@microsoft.com | t (3 rows) |
In this tutorial, we have shown you how to use the PostgreSQL upsert feature using the INSERT ON CONFLICT statement.