---
source: https://iinventedlight.com/library/notes/sqlite-upsert.md
title: "UPSERT in SQLite"
license: CC-BY-4.0
license_url: https://creativecommons.org/licenses/by/4.0/
source_license: "Public domain (sqlite.org); prose restates cited source lines"
attribution: "Summary by iinventedlight (AI-assisted). Sources: SQLite (sqlite.org), UPSERT, Public domain (sqlite.org); SQLite (sqlite.org), INSERT, Public domain (sqlite.org)."
author: "iinventedlight (synthesis of cited sources); text drafted with AI assistance"
date: 2026-11-04
status: published
checked_on: 2026-11-04
as_of: 2026-11-04
confidence: medium
untested: "No code was run for this note; only the fetched documentation pages were read."
category: coding-references
topic_area: "code-ai-eng: SQLite"
tags: ['sqlite', 'sql', 'upsert', 'insert']
sources:
  - id: S1
    title: "UPSERT"
    publisher: SQLite (sqlite.org)
    url: https://www.sqlite.org/lang_upsert.html
    accessed: 2026-11-04
    license: "Public domain (sqlite.org)"
  - id: S2
    title: "INSERT"
    publisher: SQLite (sqlite.org)
    url: https://www.sqlite.org/lang_insert.html
    accessed: 2026-11-04
    license: "Public domain (sqlite.org)"
---
# UPSERT in SQLite

**Answer:** The syntax follows PostgreSQL.

```sql
CREATE TABLE vocab (word TEXT PRIMARY KEY, count INT DEFAULT 1);

INSERT INTO vocab(word) VALUES ('jovial')
  ON CONFLICT(word) DO UPDATE SET count = count + 1;

INSERT INTO phonebook(name, phone) VALUES ('Alice', '555')
  ON CONFLICT(name) DO UPDATE SET phone = excluded.phone
  WHERE excluded.phone <> phonebook.phone;
```

## Rules

- UPSERT applies only to uniqueness constraints (PRIMARY KEY, UNIQUE), not NOT NULL or CHECK.
- An INSERT can have several ON CONFLICT clauses; only the last may omit the target.
- Not allowed after `INSERT ... DEFAULT VALUES`.
- With `INSERT ... SELECT`, add a `WHERE true` to avoid a parsing ambiguity.

## Claims
- id: C1
  claim: UPSERT follows syntax established by PostgreSQL.
  source: S1
  evidence: "syntax established by PostgreSQL, with generalizations."
- id: C2
  claim: UPSERT processing happens only for uniqueness constraints.
  source: S1
  evidence: "The UPSERT processing happens only for uniqueness constraints."
- id: C3
  claim: An INSERT may have one or more ON CONFLICT clauses.
  source: S1
  evidence: "one or more ON CONFLICT clauses, as shown in the syntax diagram above."
- id: C4
  claim: The upsert-clause is not supported after DEFAULT VALUES.
  source: S2
  evidence: "The upsert-clause is not supported after DEFAULT VALUES."

## Sources
- [S1] UPSERT. SQLite (sqlite.org). https://www.sqlite.org/lang_upsert.html . License: Public domain (sqlite.org). Accessed 2026-11-04.
- [S2] INSERT. SQLite (sqlite.org). https://www.sqlite.org/lang_insert.html . License: Public domain (sqlite.org). Accessed 2026-11-04.

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