Insert, update, and delete data

-- multi-row values
INSERT INTO users VALUES (1, 'ada'), (2, 'grace');
-- explicit columns + expressions
INSERT INTO orders (id, user_id, total, placed_at)
VALUES (101, 1, 59.99, now()), (102, 1, 12.50, now());
-- defaults
INSERT INTO users VALUES (DEFAULT, DEFAULT);   -- via DEFAULT VALUES form below
INSERT INTO users (id, name) VALUES (3, 'linus');
-- from a query
INSERT INTO archive_orders SELECT * FROM orders WHERE placed_at < DATE '2025-01-01';
-- idempotent replays
INSERT INTO users VALUES (1, 'ada') ON CONFLICT (id) DO NOTHING;
INSERT INTO users VALUES (1, 'ADA') ON CONFLICT (id) DO UPDATE SET name = EXCLUDED.name;
-- echo what happened
INSERT INTO users VALUES (4, 'hedy') RETURNING id, name;

UPDATE users SET name = 'ADA' WHERE id = 1 RETURNING id, name;
UPDATE users AS u SET name = upper(name) FROM orders o WHERE o.user_id = u.id;
UPDATE events SET payload['team'] = '"engine"' WHERE id = 7;  -- JSON subscript target

DELETE FROM orders WHERE placed_at < DATE '2024-01-01' RETURNING id;
DELETE FROM orders USING users WHERE orders.user_id = users.id AND users.name = 'test';
TRUNCATE orders;