postgresql · 2026-09-21

Upsert in postgres

Syntax

INSERT INTO <table_name> (column1, column2, column3)
VALUES (1, 'foo', 'bar'), (2, 'jazz', 'chaz')
ON CONFLICT (on_conflict_column);
DO NOTHING | DO UPDATE SET column1 = value1, column2 = value2, column3 = value3;

Examples

postgres@localhost:postgres> \d grades
+----------+-------------------+------------------------------------------------------+
| Column   | Type              | Modifiers                                            |
|----------+-------------------+------------------------------------------------------|
| id       | integer           |  not null default nextval('grades_id_seq'::regclass) |
| fullname | character varying |                                                      |
| grade    | smallint          |                                                      |
| subject  | character varying |                                                      |
+----------+-------------------+------------------------------------------------------+
Indexes:
    "grades_pkey" PRIMARY KEY, btree (id)
    "unique_fullname_subject" UNIQUE CONSTRAINT, btree (fullname, subject)

Time: 0.007s
postgres@localhost:postgres> select * from grades;
+----+--------------+-------+------------+
| id | fullname     | grade | subject    |
|----+--------------+-------+------------|
| 1  | John Doe     | 6     | Math       |
| 2  | Jane Doe     | 3     | Math       |
| 3  | John Jones   | 7     | Math       |
| 4  | Kate Doe     | 3     | Math       |
| 5  | Tom Jones    | 9     | Literature |
| 6  | Kate Strong  | 8     | Literature |
| 7  | Hans Black   | 9     | Literature |
| 8  | Jack Strong  | 7     | Literature |
| 9  | Jack Black   | 9     | Biology    |
| 10 | Lilly Strong | 9     | Biology    |
+----+--------------+-------+------------+
SELECT 10
Time: 0.005s

Do nothing

There's already a PK with the value 10, so there's no insertion

postgres@localhost:postgres> insert into grades values (10, 'Lilly Strong', 10, 'Biology') on conflict do nothing;
INSERT 0 0
Time: 0.001s

Update on conflict

Fails without the ON CONFLICT clause since there's a unique constraint for (fullname, subject).

postgres@localhost:postgres> insert into grades (fullname, grade, subject) values ('Tom Jones', 10, 'Literature');
duplicate key value violates unique constraint "unique_fullname_subject"
DETAIL:  Key (fullname, subject)=(Tom Jones, Literature) already exists.
Time: 0.002s

The existing row is updated when ON CONFLICT ... DO UPDATE is used.

postgres@localhost:postgres> insert into grades (fullname, grade, subject)
 values ('Tom Jones', 10, 'Literature')
 on conflict (fullname, subject) do update set grade = excluded.grade;
INSERT 0 1
Time: 0.003s