*******
DISCLAIMER!!! There is no such statement called UPSERT
!
*******
What is UPSERT
?
It is a combination of INSERT
and UPDATE
. This idea allows us to update an existing row or insert a new one if it doesn’t exist.
Let’s start from the very basic syntax and move from there!
Note that the features we are using in this article is only available for PostgreSQL 9.5+.
Basic Syntax
Basic is always a good place to start!
As I have said above, there is no such statement called UPSERT
. It is done through INSERT...ON CONFLICT
statement.
Here’s the basic syntax.
INSERT INTO table_name (column1, column2, ...)
VALUES
(col1_value, col2_value, ...)
ON CONFLICT (conflict_column)
DO NOTHING | DO UPDATE SET
column1 = col1_value,
column2 = col2_value, ...;
table_name
: The name of the table into which we want to insert into.(column1, column2, ...)
: The list of columns we want to insert.VALUES (col1_value, col2_value, ...)
: The values we want to insert.ON CONFLICT (conflict_column):
The conflict target column name. This column (or columns) has to be a unique constraint or unique index that may cause a conflict.DO NOTHING
: Just like the name suggests, do nothing when a conflict occurs.DO UPDATE
: Performs an update on conflictSET column = col1_value, column = col2_value, ...
: This list of the columns to be updated and their corresponding values in case of conflict.
Simple Example
Let’s try it out with a simple example where we have a test_user
table that is defined like following.
CREATE TABLE test_user(
id INT PRIMARY KEY,
name VARCHAR(255) NOT NULL,
age INT not NULL
);
And insert some sample data into it!
INSERT INTO public.test_user (id, "name", age)
values
(0, 'itsuki0', 100000),
(1, 'itsuki1', 100000),
(2, 'itsuki2', 100000),
(3, 'itsuki3', 100000),
(4, 'itsuki4', 100000),
(5, 'itsuki5', 100000)
RETURNING *;
Time to try out UPSERT
!
In the case of a non-existing row, no conflict occurs and the row is inserted!
INSERT INTO public.test_user (id, "name", age)
values
(6, 'itsuki6', 100001)
ON CONFLICT(id)
DO UPDATE SET
age = 100001;
For an existing row, the age is updated to its new value.
INSERT INTO public.test_user (id, "name", age)
values
(0, 'itsuki0', 1000000000)
ON CONFLICT(id)
DO UPDATE SET
age = 1000000000;
UPSERT For Multiple Rows
If you think about the syntax we have above carefully, you will realize that it will NOT work if we try to insert multiple rows where each row has a different target value!
We cannot DO UPDATE SET
multiple rows by specifying the value like we did above, ie: age = someNumber
, or name = someName
.
Here is where the EXCLUDE
comes in handy. I think of it as a special table that keeps a reference to the values we attempted to insert!
Here is how we can use it to insert/update multiple rows all at once!
INSERT INTO public.test_user (id, "name", age)
values
(0, 'itsuki0', 1),
(1, 'itsuki1', 2),
(2, 'itsuki2', 3),
(3, 'itsuki3', 4),
(100, 'itsuki100', 100),
(101, 'itsuki101', 101)
ON CONFLICT(id)
DO UPDATE SET
age = EXCLUDED.age;
And here is what we end up with!
Thank you, EXCLUDE
!
Thank you for reading!
That’s all I have for today!
Happy UPSERT
-ing!