Insert Update Delete

INSERT INTO users (email) VALUES ('a@b.co')
RETURNING id, created_at;
-- id | created_at

UPDATE orders o
SET status = 'paid'
FROM payments p
WHERE p.order_id = o.id AND p.ok;
-- mark paid orders

DELETE FROM sessions s
USING users u
WHERE s.user_id = u.id AND u.banned;
-- drop banned users' sessions

INSERT INTO kv (k, v) VALUES ('a', 1)
ON CONFLICT (k) DO UPDATE
SET v = excluded.v
RETURNING *;
-- upserted row

INSERT INTO archive
SELECT * FROM events WHERE created_at < now() - interval '90 days';
-- rows inserted: n

UPDATE users SET name = 'Ada'
WHERE id = 1
RETURNING id, name;
-- 1 | Ada

DELETE FROM jobs WHERE done
RETURNING id;
-- deleted ids

INSERT INTO t DEFAULT VALUES RETURNING *;
-- row with defaults

INSERT INTO tags (name) VALUES ('a'), ('b'), ('c');
-- 3 rows

INSERT INTO emails (addr) VALUES ('x@y.z')
ON CONFLICT DO NOTHING;
-- 0 or 1 row

UPDATE products SET updated_at = now();
-- all products touched

WITH doomed AS (
  SELECT id FROM jobs WHERE attempts > 5
)
DELETE FROM jobs j USING doomed d WHERE j.id = d.id;
-- purge retries

MERGE INTO target t
USING source s ON t.id = s.id
WHEN MATCHED THEN UPDATE SET v = s.v
WHEN NOT MATCHED THEN INSERT (id, v) VALUES (s.id, s.v);

-- COPY t (a,b) FROM '/tmp/t.csv' WITH (FORMAT csv, HEADER true);
-- rows loaded: n

TRUNCATE TABLE staging RESTART IDENTITY;
-- staging empty