RETURNING & Bulk Loading

RETURNING lets a write hand rows back to you immediately. Instead of guessing which id was generated, or running a second query to see what changed, you can ask PostgreSQL to return the columns you care about from the rows it just wrote.

Bulk loading is the same discipline at a larger shape: move many rows with one set-based statement or a file-loading command, then verify what landed. In this browser lab, live bulk-shaped demos use staging tables and INSERT ... SELECT; real file loading with COPY and psql's \copy is shown as a transcript because the playground has no file picker.

INSERT RETURNING: read generated values

The order queue has an identity primary key, a default status, and a default date. Insert one order and return the generated and defaulted values in the same statement:

INSERT INTO customer_orders (customer_name, item_name, quantity)
VALUES ('Rosa', 'Cold Brew Kit', 1)
RETURNING order_id, customer_name, item_name, quantity, status, created_on;

 order_id | customer_name |   item_name   | quantity | status | created_on
----------+---------------+---------------+----------+--------+------------
        1 | Rosa          | Cold Brew Kit |        1 | queued | 2026-05-05
(1 row)

That grid is the write's answer. order_id, status, and created_on were not supplied by the insert; the table filled them, and RETURNING made the filled row visible immediately.

Use RETURNING for facts the database owns: generated ids, defaults, normalized values, or columns changed by the write. Avoid returning every column by habit. Like a SELECT list, a RETURNING list should say what the next step actually needs.

UPDATE RETURNING: verify after rehearsal

For updates, RETURNING is verification, not rehearsal. You still inspect the target first, because the returned rows arrive after the change has already happened.

SELECT order_id, customer_name, item_name, status
FROM customer_orders
WHERE order_id = 1;
 
UPDATE customer_orders
SET status = 'packed'
WHERE order_id = 1
RETURNING order_id, customer_name, status;

 order_id | customer_name |   item_name   | status
----------+---------------+---------------+--------
        1 | Rosa          | Cold Brew Kit | queued
(1 row)
 order_id | customer_name | status
----------+---------------+--------
        1 | Rosa          | packed
(1 row)

The first grid proves the target. The returned grid proves the new state. If the rehearsal had shown two rows, the right move would be to tighten the WHERE clause before running the update.

INSERT SELECT: load from a staging table

File imports often land in a staging table first: raw rows in, then a set-based insert into the real table after validation or transformation. This live example uses the staged rows already loaded in setup:

INSERT INTO customer_orders (customer_name, item_name, quantity, created_on)
SELECT so.customer_name,
       so.item_name,
       so.quantity,
       so.created_on
FROM staged_orders AS so
WHERE so.quantity > 0
ORDER BY so.customer_name
RETURNING order_id, customer_name, item_name, quantity, created_on;

 order_id | customer_name |   item_name    | quantity | created_on
----------+---------------+----------------+----------+------------
        2 | Ada           | Espresso Beans |        2 | 2026-05-06
        3 | Ken           | Croissant      |        5 | 2026-05-06
        4 | Priya         | Earl Grey      |        1 | 2026-05-06
(3 rows)

This is "bulk" in the set-based SQL sense: one statement copies every qualifying row from one relation into another. It is structurally different from sending one insert per row from application code. The database can parse one statement, apply one filter, and return the rows that landed.

DELETE RETURNING: last look, after the target check

When removing rows, the rehearsal grid is still the last safe look before deletion. Here Priya's order was a duplicate test order, so the target is inspected first:

SELECT order_id, customer_name, item_name, status
FROM customer_orders
WHERE customer_name = 'Priya'
ORDER BY order_id;
 
DELETE FROM customer_orders
WHERE customer_name = 'Priya'
RETURNING order_id, customer_name, item_name, status;
 
SELECT status, count(*) AS orders
FROM customer_orders
GROUP BY status
ORDER BY status;

 order_id | customer_name | item_name | status
----------+---------------+-----------+--------
        4 | Priya         | Earl Grey | queued
(1 row)
 order_id | customer_name | item_name | status
----------+---------------+-----------+--------
        4 | Priya         | Earl Grey | queued
(1 row)
 status | orders
--------+--------
 packed |      1
 queued |      2
(2 rows)

RETURNING makes the removed row visible after the delete. The final count is a separate verification query: the queue now has one packed order and two queued orders.

COPY and \copy: file loading transcripts

For real CSV files, PostgreSQL's bulk-loading command is COPY. There are two spellings beginners see:

-- server-side COPY: the PostgreSQL server reads a path it can access
postgres=# COPY staged_orders (customer_name, item_name, quantity, created_on)
postgres-# FROM '/var/lib/postgresql/import/orders.csv'
postgres-# WITH (FORMAT csv, HEADER true);
COPY <rows_loaded>

-- psql \copy: the psql client reads your local file and sends the rows
cafe=> \copy staged_orders (customer_name, item_name, quantity, created_on) FROM 'orders.csv' WITH (FORMAT csv, HEADER true)
COPY <rows_loaded>

Those are transcript shapes, not playground code. In a real terminal, PostgreSQL prints COPY followed by the number of rows loaded from that file. COPY talks about files, and this browser editor has no file chooser. \copy is not SQL at all; it is a psql meta-command, so it belongs in a terminal, not in application SQL and not in this playground.

When a course project gives you CSV files to load into local PostgreSQL, use this same shape: connect with psql, create the table from the project schema, load the file into a staging table with \copy, then run the verification query the project asks for. The browser lessons hide that file step behind seeds; local work makes it explicit.

The workflow is still the same as the live example:

• Load raw rows into a staging table.
• Inspect row counts and suspicious values.
• Use INSERT ... SELECT to copy clean rows into the real table.
• Verify the inserted rows with RETURNING or a follow-up SELECT.

Section 8 gave you the write side: insert rows, change rows safely, upsert, merge, return what changed, and load batches through staging. The next milestone project combines those skills with joins and subqueries in a small library system.