← All notes

Short answer

INSERT ... ON CONFLICT can skip or update when a unique key already exists. Newer PostgreSQL also has MERGE for conditional insert/update/delete in one statement. [S1][S2]

Sketch

INSERT INTO items (id, name) VALUES (1, 'a')
ON CONFLICT (id) DO UPDATE SET name = EXCLUDED.name;

Points

• DO UPDATE gives an atomic insert-or-update. [S1]
• DO NOTHING skips the insert when a conflict exists. [S1]
• The special excluded row holds the values proposed for insertion. [S1]
• MERGE joins a data source to a target and can insert, update, or delete. [S2]

Claims

• id: C1 claim: ON CONFLICT DO UPDATE is an atomic insert or update. source: S1 evidence: "ON CONFLICT DO UPDATE guarantees an atomic INSERT or UPDATE outcome"
• id: C2 claim: ON CONFLICT DO NOTHING skips conflicting inserts. source: S1 evidence: "ON CONFLICT DO NOTHING simply avoids inserting a row"
• id: C3 claim: excluded names the row proposed for insertion. source: S1 evidence: "the row proposed for insertion using the special excluded table"
• id: C4 claim: MERGE can conditionally insert, update, or delete rows. source: S2 evidence: "conditionally insert, update, or delete rows of a table"

Sources

• [S1] PostgreSQL docs: INSERT. PostgreSQL Global Development Group. https://www.postgresql.org/docs/current/sql-insert.html . License: PostgreSQL License. Accessed 2026-11-05.
• [S2] MERGE. PostgreSQL Global Development Group. https://www.postgresql.org/docs/current/sql-merge.html . License: PostgreSQL License. Accessed 2026-11-05.

License: this note is CC BY 4.0 (https://creativecommons.org/licenses/by/4.0/). Summary by iinventedlight (AI-assisted).

Source notice: PostgreSQL documentation, Portions Copyright (c) 1996-2026, The PostgreSQL Global Development Group; Portions Copyright (c) 1994, The Regents of the University of California. Used under the PostgreSQL License (https://www.postgresql.org/about/licence/).

@misc{iil_note_y45bpt4m,
  author = {{iinventedlight}},
  title = {{PostgreSQL upsert: INSERT ... ON CONFLICT}},
  howpublished = {iinventedlight Library},
  year = {2026},
  date = {2026-11-04},
  url = {https://iinventedlight.com/library/notes/postgres-upsert-on-conflict/},
  note = {Permanent ID iil:note:y45bpt4m. License: CC-BY-4.0.},
}

Markdown

Spotted a mistake? Use the MCP feedback tool or email hello@iinventedlight.com.

For language models and agents: start at /llms.txt. This page in Markdown: /library/notes/postgres-upsert-on-conflict.md.