Postgres Practice 4/12

RETURNING: get the row back from an INSERT

You insert a new order and need its id right away - to link a payment record to it, to show it in a confirmation message, whatever comes next. The id column is SERIAL, so the database picks its value; without RETURNING you'd have to run a second query afterward to find it (and, depending on what else is happening, you can't always be sure which row is "the one you just inserted"). RETURNING solves this by handing the row straight back as part of the INSERT itself.

(SQLite added its own RETURNING clause a few years after Postgres - so this isn't exclusive to Postgres today, but it's the exact same idea Postgres popularized, and it's worth knowing either way.)

You have an orders table: id (SERIAL PRIMARY KEY), item, amount.

Your task: insert a new order for 'Notebook' at amount 5, and return its generated id and item in the same statement.

You'll practice:

Adding RETURNING to an INSERT
Getting a generated column's value without a follow-up SELECT

-- Insert a new order for 'Notebook' at amount 5, then look at what came back.
INSERT INTO orders (item, amount) VALUES ('Notebook', 5);