INSERT, UPDATE, DELETE, RETURNING

Write data to the database — and use RETURNING to get results back without a second query

So far you have only read data. INSERT, UPDATE, and DELETE write to the database — and unlike SELECT, they cannot be undone without a transaction.\n\nThe most dangerous mistake is forgetting WHERE in an UPDATE or DELETE. A bare DELETE FROM beers removes all 400 rows silently. A bare UPDATE beers SET abv = 0 zeroes every ABV value.\n\nRETURNING is a PostgreSQL extension that makes write statements return the affected rows, just like a SELECT. This avoids the common pattern of writing a row and then immediately running another SELECT to fetch the generated ID.

INSERT — Adding Rows

-- Single row insert
INSERT INTO styles (name, abv_range, ibu_range)
VALUES ('New England IPA', numrange(6.0, 8.0, '[)'), numrange(40, 70, '[)'));

-- Multi-row insert
INSERT INTO styles (name, abv_range, ibu_range) VALUES
  ('Brut IPA',  numrange(6.0, 7.5, '[)'), numrange(20, 40, '[)')),
  ('Cold IPA',  numrange(6.0, 7.5, '[)'), numrange(40, 60, '[)'));

-- INSERT … SELECT: copy rows from a query
INSERT INTO styles (name, abv_range, ibu_range)
SELECT 'Lager Mix', abv_range, ibu_range
FROM styles
WHERE name = 'German Pilsner';

RETURNING — Get the Row Back

INSERT INTO styles (name, abv_range, ibu_range)
VALUES ('Kellerbier', numrange(4.5, 5.5, '[)'), numrange(20, 35, '[)'))
RETURNING id, name;
-- Returns the auto-generated id without a follow-up SELECT

UPDATE — Modifying Rows

-- Always include WHERE
UPDATE beers
SET description = 'Updated description for this imperial stout.'
WHERE style = 'Imperial Stout'
  AND name = 'Reserve Porter'
RETURNING name, description;

DELETE — Removing Rows

-- Targeted delete using a subquery — no need to look up the id first
DELETE FROM styles
WHERE name = 'Kellerbier'
RETURNING name;

ON CONFLICT DO NOTHING — Safe Upsert

INSERT INTO styles (name, abv_range, ibu_range)
VALUES ('American IPA', numrange(5.5, 7.5, '[)'), numrange(40, 70, '[)'))
ON CONFLICT (name) DO NOTHING;
-- Silently skips if 'American IPA' already exists

RETURNING clause

A PostgreSQL extension to INSERT, UPDATE, and DELETE that returns the affected rows as a result set. You can use RETURNING * to get all columns, or list specific columns. This eliminates the common pattern of writing a row and then running a SELECT to fetch the generated ID or updated values. It is evaluated after the write operation completes, so it reflects the final stored values.

DML (Data Manipulation Language)

The subset of SQL for reading and writing data: SELECT, INSERT, UPDATE, DELETE. Contrasted with DDL (Data Definition Language: CREATE, ALTER, DROP) which modifies the schema itself. INSERT, UPDATE, and DELETE without a transaction can be immediately visible to other sessions — wrapping them in BEGIN/COMMIT gives you the ability to roll back.

🔌 Connect to beer_db

Connect to beer_db as the beer_db user. INSERT, UPDATE, and DELETE exercises modify the beers and styles tables.

psql -U beer_db -d beer_db

psql (18.4) Type "help" for help. beer_db=>

➕ INSERT a New Style

Insert a new row into the styles table for "New England IPA" with abv_range [6.0, 8.0) and ibu_range [40, 70). Use RETURNING id, name to see the auto-generated UUID without a follow-up SELECT.

INSERT INTO styles (name, abv_range, ibu_range) VALUES ('New England IPA', numrange(6.0, 8.0, '[)'), numrange(40, 70, '[)')) RETURNING id, name;

id | name --------------------------------------+----------------- xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx | New England IPA (1 row) INSERT 0 1

🛡️ ON CONFLICT DO NOTHING — Safe Re-insert

Try to insert "New England IPA" again. Without ON CONFLICT it would fail with a unique violation. Add ON CONFLICT (name) DO NOTHING — the duplicate is silently skipped.

INSERT INTO styles (name, abv_range, ibu_range) VALUES ('New England IPA', numrange(6.0, 8.0, '[)'), numrange(40, 70, '[)')) ON CONFLICT (name) DO NOTHING RETURNING id, name;

INSERT 0 0

✏️ UPDATE with RETURNING

Update the description of the "New England IPA" style to "Hazy, juicy IPA with low bitterness and tropical fruit aroma." Use a subquery in WHERE to find it by name — no need to copy-paste the UUID. Use RETURNING name, description to confirm the change.

UPDATE styles SET description = 'Hazy, juicy IPA with low bitterness and tropical fruit aroma.' WHERE name = 'New England IPA' RETURNING name, description;

name | description -----------------+-------------------------------------------------------- New England IPA | Hazy, juicy IPA with low bitterness and tropical fruit aroma. (1 row) UPDATE 1

🗑️ DELETE with RETURNING

Delete the "New England IPA" style row you created. Use WHERE name = 'New England IPA' and RETURNING name to confirm what was removed.

DELETE FROM styles WHERE name = 'New England IPA' RETURNING name;

name ----------------- New England IPA (1 row) DELETE 1

📋 INSERT … SELECT — Copy Rows from a Query

Insert 3 new beers into the beers table using INSERT … SELECT. Copy the brewery_id of "Golden Brewery" using a subquery — no need to look up the UUID manually. The new beers should be: "Hazy Pils" (style "German Pilsner", abv 4.9), "Dark Wave" (style "American Stout", abv 5.8), and "Citrus Burst" (style "American IPA", abv 6.2). Use RETURNING name, style, abv to confirm all three were inserted.

INSERT INTO beers (brewery_id, name, style, abv) SELECT (SELECT id FROM breweries WHERE name = 'Golden Brewery'), v.name, v.style, v.abv FROM (VALUES ('Hazy Pils', 'German Pilsner', 4.9), ('Dark Wave', 'American Stout', 5.8), ('Citrus Burst', 'American IPA', 6.2)) AS v(name, style, abv) RETURNING name, style, abv;

Lab complete! You can now safely write data to PostgreSQL:\n\n\n INSERT INTO … VALUES : ✅ single and multi-row\n INSERT INTO … SELECT : ✅ copy from a query result\n UPDATE … WHERE : ✅ always targeted\n DELETE … WHERE : ✅ always targeted\n RETURNING : ✅ confirms what was written\n ON CONFLICT DO NOTHING : ✅ safe re-insert\n

Enable JavaScript to run the live terminal and track your progress.