INSERT & Returning
Overview
INSERT adds new rows to a table. The basic syntax is `INSERT INTO table (col1, col2) VALUES (val1, val2);`. By appending the `RETURNING` clause (e.g., `RETURNING id, name` or `RETURNING *` for all columns), you can immediately get back the data that was inserted, which is incredibly useful for retrieving auto-generated IDs or default values without needing a second SELECT query. Think of RETURNING as an INSERT and a SELECT merged into one step. Note: `RETURNING` is specific to PostgreSQL and SQLite (SQL Server uses `OUTPUT` instead).
Every application needs to create data. Using RETURNING prevents race conditions and saves a database round-trip when you need the newly created record's details for retrieval.
Where used: user registration, checkout systems, data ingestion pipelines
Why learn this
- You can insert single or multiple rows in one command.
- You can retrieve newly generated primary keys instantly to use them as foreign keys in other tables.
Query walkthrough
INSERT INTO products (id, name, category, price) VALUES (9, 'Pen', 'Stationery', 2.99) RETURNING id, name, price;Focus: The immediate projection of the inserted row via the RETURNING clause.
Common mistakes
- Column order mismatch: Providing VALUES in a different order than the column list. Always explicitly list columns to ensure values match.
- Missing required fields: Failing to provide a value for a column that does not have a default and cannot be NULL (like primary keys or required names).
Glossary
- retrieval
- The process of getting data back from the database after it has been stored.
- explicitly
- Stating something clearly and directly, leaving no room for confusion or guessing.
Recall questions
- What is the benefit of adding the RETURNING clause to an INSERT statement?
Understanding checks
What rows does this query return?
id: 9, name: 'Ian'
The INSERT statement adds the new row, and RETURNING id, name projects only those two columns for the newly inserted row.
What rows does this query return?
id: 7, name: 'Tablet' id: 8, name: 'Lamp'
Multiple rows are inserted, and RETURNING gives back the id and name for both newly created rows.
Practice tasks
Insert a new order
Given this incomplete INSERT statement, modify it to add order ID 9 for customer 1 on '2023-09-10' with status 'pending', and return all columns of the new order.
Challenge
Insert and Return
Write a query to insert a new product: id 10, name 'Headphones', category 'Electronics', price 99.99. Return the name and category of the inserted product.
Continue learning
Previous: Constraints & defaults
Next: UPSERT (ON CONFLICT)