Add rows with INSERT, get generated values back with RETURNING, change rows with UPDATE, remove them with DELETE, and handle duplicates cleanly with an ON CONFLICT upsert.
Why: INSERT adds new rows. You list the columns you are filling, then the values. Any column you skip falls back to its DEFAULT (or NULL). You can add many rows in one statement by separating value lists with commas. Note: the multi-row inserts below are also the sample data for the rest of the course — run them once, in order, on the fresh tables from lesson 2 (you also need the last_login column the ALTER lesson added). IDs are auto-assigned in order, so users become 1–7, products 1–7, orders 1–8 — which is what the later queries refer to.
INSERT INTO users (email, full_name)
VALUES ('ada@example.com', 'Ada Lovelace');-- the rest of our users (ids 2–7), with country, status and last login
INSERT INTO users (email, full_name, country, is_active, last_login) VALUES
('alan@example.com', 'Alan Turing', 'UK', true, now() - interval '2 days'),
('grace@example.com', 'Grace Hopper', 'USA', true, now() - interval '10 days'),
('edsger@example.com', 'Edsger Dijkstra','NL', true, NULL),
('donald@example.com', 'Donald Knuth', 'USA', false, now() - interval '400 days'),
('barbara@example.com','Barbara Liskov', 'USA', true, NULL),
('tim@example.com', 'Tim Berners-Lee','UK', true, now() - interval '1 day');-- products (ids 1–7) across three categories, some tagged with a colour
INSERT INTO products (sku, name, category, price, metadata) VALUES
('BK-001', 'SQL Basics', 'Books', 29.99, '{"color": "red"}'),
('BK-002', 'PostgreSQL Deep Dive', 'Books', 45.00, '{"color": "blue"}'),
('EL-001', 'USB-C Cable', 'Electronics', 9.99, '{"color": "black"}'),
('EL-002', 'Wireless Mouse', 'Electronics', 24.50, '{"color": "red"}'),
('EL-003', 'Mechanical Keyboard', 'Electronics', 79.00, '{"color": "white"}'),
('CL-001', 'T-Shirt', 'Clothing', 15.00, '{"color": "red"}'),
('CL-002', 'Hoodie', 'Clothing', 39.99, '{"color": "green"}');-- orders (ids 1–8); users 1 and 6 are "big spenders" (> 1000 total)
INSERT INTO orders (user_id, total, created_at) VALUES
(1, 1200.00, now() - interval '30 days'),
(1, 150.00, now() - interval '20 days'),
(2, 45.00, now() - interval '15 days'),
(2, 900.00, now() - interval '5 days'),
(3, 30.00, now() - interval '12 days'),
(3, 75.50, now() - interval '3 days'),
(6, 2000.00, now() - interval '1 day'),
(7, 60.00, now() - interval '2 days');-- line items linking orders to products
INSERT INTO order_items (order_id, product_id, quantity) VALUES
(1, 2, 1),
(1, 5, 2),
(2, 1, 3),
(3, 3, 1),
(4, 5, 1),
(7, 6, 4),
(8, 7, 2);-- an org chart: employee 1 is the root, everyone else reports upward
INSERT INTO employees (name, manager_id) VALUES
('Ada (CEO)', NULL),
('Brian (VP)', 1),
('Carol (VP)', 1),
('Dan (Lead)', 2),
('Erin (Eng)', 4),
('Frank (Eng)', 4),
('Grace (Eng)', 3);-- accounts 1 and 2 are used by the money-transfer transaction later
INSERT INTO accounts (owner, balance) VALUES
('Checking', 1000.00),
('Savings', 500.00),
('Reserve', 0.00);Why: the database generates some values for you — like the auto id and created_at. RETURNING hands those back in the same statement, so you do not need a second query to find out the new row’s id.
INSERT INTO users (email, full_name)
VALUES ('linus@example.com', 'Linus T.')
RETURNING id, created_at;Why: UPDATE changes values in rows that match the WHERE clause. Note: this is the dangerous one — if you forget WHERE, every row in the table is updated. Always run a SELECT with the same WHERE first to see what you will hit.
UPDATE users
SET full_name = 'Ada Byron',
last_login = now()
WHERE email = 'ada@example.com';Why: DELETE removes rows that match the WHERE clause. Same warning as UPDATE: no WHERE means you delete the whole table’s contents.
DELETE FROM users
WHERE is_active = false;Why: sometimes you want to "insert this row, but if one with the same unique value already exists, update it instead". That is an upsert. Note: EXCLUDED refers to the row you tried to insert, so you can copy its values onto the existing row.
INSERT INTO users (email, full_name)
VALUES ('ada@example.com', 'Ada L.')
ON CONFLICT (email)
DO UPDATE SET full_name = EXCLUDED.full_name;