Skip to content

Upsert statement #279

@danhhz

Description

@danhhz

Just leaving this here while it's all still fresh

  • "Upsert" is a general term for an insert-or-on-conflict-update
  • The conflict part is determined by a unique index
  • Postgres and Mysql each have different syntax (there's nothing in the SQL standard)
  • We support the postgres style (CREATE TABLE kv (k INT PRIMARY KEY, v INT); INSERT INTO kv VALUES (1, 2), (3, 4) ON CONFLICT (k) DO UPDATE SET v = excluded.v)
  • Postgres exposes a special table called "excluded" that contains whatever was in the row being inserted. In the example above, "excluded.v" references the 2 and then the 4.
  • UPSERT is cockroach-specific syntactic sugar. It uses the primary key as the index in ON CONFLICT and adds a clause in SET (like v = excluded.v) for each field in the insert tuple that's not in the conflict index. UPSERT INTO kv VALUES (1, 2), (3, 4) is equivalent to the example above.
  • gotcha: It's probably a bad idea to name a database table "excluded"
  • gotcha: As with postgres, you can't modify the same row twice in one upsert. So UPSERT INTO kv VALUES (1, 2), (1, 3) doesn't work
  • gotcha: If you upsert into a table with more than one unique index, it's still possible to get uniqueness violations (if the values in your SET violate some other index than the conflict index for example)
  • There will (very soon) be a significant performance optimization for upserts on tables with no secondary indexes and all columns specified.

Metadata

Metadata

Assignees

Labels

No labels
No labels

Type

No type

Projects

No projects

Milestone

No milestone

Relationships

None yet

Development

No branches or pull requests

Issue actions