---
source: https://iinventedlight.com/library/notes/postgres-upsert-on-conflict.md
title: "PostgreSQL upsert: INSERT ... ON CONFLICT"
license: CC-BY-4.0
license_url: https://creativecommons.org/licenses/by/4.0/
source_license: "PostgreSQL License; restated in our own words"
attribution: "Summary by iinventedlight (AI-assisted). Sources: PostgreSQL Global Development Group, PostgreSQL docs: INSERT, PostgreSQL License; PostgreSQL Global Development Group, MERGE, PostgreSQL License."
author: "iinventedlight (synthesis of cited sources); text drafted with AI assistance"
date: 2026-11-04
status: published
checked_on: 2026-11-05
as_of: 2026-11-05
confidence: medium
untested: "Steps and code were not run; only the fetched documentation page was read."
category: coding-references
topic_area: "code-backend: insert-or-update in SQL"
tags: sql, postgresql, upsert, database
sources:
  - id: S1
    title: "PostgreSQL docs: INSERT"
    publisher: PostgreSQL Global Development Group
    url: https://www.postgresql.org/docs/current/sql-insert.html
    accessed: 2026-11-05
    license: "PostgreSQL License"
  - id: S2
    title: "MERGE"
    publisher: PostgreSQL Global Development Group
    url: https://www.postgresql.org/docs/current/sql-merge.html
    accessed: 2026-11-05
    license: "PostgreSQL License"
    license: "PostgreSQL License"
---
# PostgreSQL upsert: ON CONFLICT and MERGE

## 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
```sql
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/).