How to Perform Upserts in Postgres
How to perform an upsert to insert or update database records in PostgreSQL. This episode is brought to you by Hashrocket, expert consultants in PostgreSQL - learn more at https://hashrocket.com and www.pgcasts.com
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.
```sql
insert into users
values ('Sam Smith', '[email protected]', 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".
```sql
insert into users
values ('Sam Smith', '[email protected]', 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.
```sql
insert into users
values ('Sam Smith', '[email protected]', 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.
```sql
insert into users
values ('Sam Smith', '[email protected]', 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!
Watch video How to Perform Upserts in Postgres online without registration, duration hours minute second in high quality. This video was added by user PG Casts by Hashrocket 25 July 2019, don't forget to share it with your friends and acquaintances, it has been viewed on our site 1,401 once and liked it 22 people.