← All notes

Answer: The syntax follows PostgreSQL.

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).

@misc{iil_note_waqakesv,
  author = {{iinventedlight}},
  title = {{UPSERT in SQLite}},
  howpublished = {iinventedlight Library},
  year = {2026},
  date = {2026-11-04},
  url = {https://iinventedlight.com/library/notes/sqlite-upsert/},
  note = {Permanent ID iil:note:waqakesv. 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/sqlite-upsert.md.