INSERT & UPSERT

INSERT

-- Single row
INSERT INTO users (id, name, email) VALUES ('u1', 'Alice', 'alice@example.com');

-- Multiple rows
INSERT INTO users (id, name) VALUES ('u1', 'Alice'), ('u2', 'Bob');

-- Object literal syntax (schemaless collections)
INSERT INTO users { name: 'Alice', email: 'alice@example.com', age: 30 };

-- INSERT ... SELECT
INSERT INTO archive SELECT * FROM orders WHERE created_at < '2025-01-01';

Plain INSERT is strict: a duplicate primary key raises unique_violation (SQLSTATE 23505). Use ON CONFLICT or UPSERT for "insert if absent" or "insert or overwrite" semantics.

INSERT ... ON CONFLICT

-- Skip rows that would collide with an existing PK (no error)
INSERT INTO users (id, name) VALUES ('u1', 'Alice')
ON CONFLICT DO NOTHING;

-- Overwrite selected fields on conflict. EXCLUDED refers to the incoming row;
-- bare column names refer to the existing row.
INSERT INTO users (id, name, login_count) VALUES ('u1', 'Alice', 1)
ON CONFLICT (id) DO UPDATE SET
  name        = EXCLUDED.name,
  login_count = users.login_count + EXCLUDED.login_count;

ON CONFLICT DO UPDATE fires AFTER UPDATE triggers (not AFTER INSERT) when a row is overwritten — the trigger op tag is derived from storage prior-bytes, not the surface SQL verb.

UPSERT

Insert or overwrite if the primary key already exists:

UPSERT INTO users (id, name, role) VALUES ('u1', 'Alice', 'admin');

-- Object literal syntax
UPSERT INTO users { id: 'u1', name: 'Alice', role: 'admin' };

UPSERT is equivalent to INSERT ... ON CONFLICT (<pk>) DO UPDATE SET <all-columns> = EXCLUDED.<col>. It fires AFTER UPDATE on overwrite and AFTER INSERT on first write.

Bulk Import

-- Auto-detected format (NDJSON, JSON array, or CSV)
COPY users FROM '/path/to/users.ndjson';
COPY users FROM '/path/to/users.csv' WITH (FORMAT csv);

See COPY for the full syntax, all format options, and COPY TO for export.