Transcript

An upsert is a statement used to either insert new records or update existing records in a database, provided some conditions are met.

In Postgres, an upsert statement can be accomplished with the use of the "on conflict" clause during an insert.

The on conflict clause allows us to specify a different behavior from insert if a unique violation or exclusion constraint violation occurs. The on conflict clause allows us to specify two different actions: do nothing, or do update.

Let's take a look at how this works.

For this example, I've created a dummy database with a table called users. The users table has a few columns, one of which is for emails and has a uniqueness and not null constraint.

\d users

ON CONFLICT DO NOTHING

I'm going to use the \e command to use vim as my query buffer editor.

By default, if no "on conflict" clause is given to an insert, an error is raised for violations. We can see this by trying to create a new user with an email that's already in our database.

insert into users
values ('Sam Smith', 'sam@example.com', 38);

Here we can see that Postgres returned an error for a uniqueness violation.

If we want to ignore such errors, we can use the "on conflict" clause and specify "do nothing".

insert into users
values ('Sam Smith', 'sam@example.com', 38)
on conflict do nothing;

We can see from the output that no changes were made from our command, but also no errors were raised.

ON CONFLICT DO UPDATE

If we want to instead update on conflict, we specify "do update". The "do update" statement requires parameters to match upon: either the conflicting column, or the constraint being violated.

To match upon column, we simply specify the column name after the on conflict statement. In our case, our unique constraint is set on email, so we're going to say on conflict (email).

After we tell Postgres we wish to update on conflict, we have to specify how the update is to be performed. This is done using the "set" command, and passing the column name and value we wish to update.

In Postgres, there's a special table called "excluded" that's used to represent rows proposed for insertion. It's from this table that we're able to get the value for our update statement.

insert into users
values ('Sam Smith', 'sam@example.com', 38)
on conflict (email) do update
set name = excluded.name, age = excluded.age;

We can see from the output now that our changes have been made.

ON CONFLICT ON CONSTRAINT

If during on conflict we want to match upon constraint instead of column, we use "on constraint", followed by the constraint we're matching upon.

insert into users
values ('Sam Smith', 'sam@example.com', 40)
on conflict on constraint users_email_key do update
set name = excluded.name, age = excluded.age;

We can see once again that our database has been updated.

Thanks for watching!

Setup

create table users (
  name varchar not null,
  email text not null unique,
  age integer
);

insert into users
values ('Sam', 'sam@example.com', 35),
('Sue', 'sue@example.com', 24),
('Carl', 'carl@example.com', 26);