INSERT, and inserting many rows at once

INSERT puts rows in. It is the simplest statement in SQL and it has three features people do not know about, each of which saves real work.

The basic form

INSERT INTO books (title, author_id, published, copies, price_paise, shelf)
VALUES ('The Vegetarian', 12, DATE '2007-10-30', 2, 39900, 'F-09');

Always name the columns. The unnamed form works:

INSERT INTO books VALUES (41, 'The Vegetarian', 12, ...);   -- don't

and it depends on column order, so adding a column to the table silently shifts every value by one. Naming them also lets you skip columns that have defaults or are nullable.

Note what is not listed: id. With GENERATED ALWAYS AS IDENTITY you cannot supply it, which module 4 showed is the point.

Many rows in one statement

INSERT INTO categories (name) VALUES
  ('Fiction'), ('Poetry'), ('Non-fiction'), ('Children');

One statement, one round trip, one transaction — all four rows or none.

This matters more than it looks. Inserting a thousand rows as a thousand statements pays the round-trip cost a thousand times; on a network that is seconds. As one statement it is one round trip.

Batch in chunks of a few hundred to a few thousand. A single statement with a hundred thousand rows produces an enormous parse tree and a very long transaction.

For genuinely large loads, COPY is the tool:

COPY books (title, author_id, published) FROM '/tmp/books.csv' WITH (FORMAT csv, HEADER);

psql -c "\copy books (title, author_id) FROM 'books.csv' WITH (FORMAT csv, HEADER)"

COPY is orders of magnitude faster than INSERT for bulk data. COPY reads a file on the server; \copy is the psql command that reads one on your machine — a distinction that confuses everybody once.

`RETURNING`, which most people do not know exists

INSERT INTO d1 (n) VALUES (1), (2), (3) RETURNING id, n;

 id | n
----+---
  1 | 1
  2 | 2
  3 | 3
(3 rows)
INSERT 0 3

The generated ids come back in the same round trip. Without it you would insert, then query for what you just inserted — which is two round trips and, worse, a guess about which rows are yours if two people insert at once.

RETURNING * gives every column, including defaults that were filled in.

It works on UPDATE and DELETE too, which the next lesson uses.

This is a PostgreSQL feature, also in MariaDB and SQLite; SQL Server spells it OUTPUT. It is one of the genuinely good reasons to know which database you are on.

Inserting from a query

INSERT INTO archived_loans (book_id, member_id, borrowed_on, returned_on)
SELECT book_id, member_id, borrowed_on, returned_on
FROM loans
WHERE returned_on < DATE '2025-01-01';

No VALUES — the rows come from a SELECT. The column counts and types must line up.

This is how you copy, archive, denormalise or backfill, and it happens entirely inside the database with no data crossing the network.

What `INSERT 0 3` means

INSERT 0 3

The second number is the row count. The first is an oid, a legacy feature that has been 0 for every normal table since PostgreSQL 12. Read the second number and ignore the first.

When it fails

Every constraint from module 4, now as things that stop an insert:

INSERT INTO books (author_id) VALUES (1);
-- ERROR:  null value in column "title" ... violates not-null constraint

INSERT INTO tags (name) VALUES ('poetry');
-- ERROR:  duplicate key value violates unique constraint "tags_pkey"
-- DETAIL:  Key (name)=(poetry) already exists.

INSERT INTO loans (book_id, member_id, ...) VALUES (999, 1, ...);
-- ERROR:  insert or update on table "loans" violates foreign key constraint
-- DETAIL:  Key (book_id)=(999) is not present in table "books".

INSERT INTO books (title, author_id, published) VALUES ('X', 1, DATE '2026-02-30');
-- ERROR:  date/time field value out of range: "2026-02-30"

A multi-row INSERT is all-or-nothing. One bad row in a thousand rolls back all of them. That is usually what you want, and when it is not, the next lesson's ON CONFLICT DO NOTHING is the tool.

Defaults

INSERT INTO members (name, joined) VALUES ('Nobody Here', current_date);

membership gets 'standard', id is generated, everything else is NULL. Only name the columns you are setting.

INSERT INTO t6 DEFAULT VALUES;      -- every column takes its default
INSERT INTO books (title, copies) VALUES ('X', DEFAULT);   -- this one column

Inserting into a table with a foreign key

Order matters: the parent must exist first.

WITH new_author AS (
  INSERT INTO authors (name, language) VALUES ('Han Kang', 'Korean') RETURNING id
)
INSERT INTO books (title, author_id, copies)
SELECT 'The Vegetarian', id, 2 FROM new_author;

A WITH containing an INSERT ... RETURNING, used by a second INSERT. Both happen in one statement, so both succeed or neither does, with no round trip in between and no window where the author exists without the book.

This is a genuinely elegant PostgreSQL feature and worth knowing early — it replaces a common three-step pattern in application code.

Performance, briefly

Things that make a bulk insert slow, in the order they matter:

One statement per row. Batch them, or use COPY.

One transaction per row. Each COMMIT has to flush to disk. Wrapping 10,000 inserts in one transaction is dramatically faster than 10,000 auto-committed ones.

Indexes. Every index must be updated per row. For a very large one-off load, dropping the indexes, loading, and recreating them can be faster overall — but only for a genuinely large load, and never on a table other people are using.

Triggers. They run per row. Know what is on the table before a bulk load.

Check your work

Why name the columns. The unnamed form depends on column order, which changes.

Why you cannot supply id. GENERATED ALWAYS AS IDENTITY refuses it, deliberately.

What multi-row VALUES buys. One round trip, and all-or-nothing.

Roughly how large a batch should be. Hundreds to low thousands.

COPY versus \copy. The server's filesystem versus yours.

What RETURNING solves. Getting the generated ids without a second query or a guess.

What INSERT ... SELECT is for. Copying, archiving and backfilling inside the database.

What the first number in INSERT 0 3 is. A legacy oid; always 0. Read the second.

What one bad row in a multi-row insert does. Rolls back all of them.

How to insert a parent and child atomically. WITH ... INSERT ... RETURNING, used by a second INSERT.

The four things that make bulk inserts slow. One statement per row, one transaction per row, indexes, triggers.

Practice

Insert a book naming the columns. Then try the unnamed form and add a column to the table between attempts.
Try to supply an explicit id. Read the error.
Insert four categories in one statement.
Insert four where the third violates a constraint. Confirm none of them landed.
Use RETURNING id and capture the generated ids.
Use RETURNING * and note the defaults that were filled in.
Create an archived_loans table and populate it with INSERT ... SELECT.
Insert 5,000 rows one statement at a time, timing it. Then as batches of 500. Then with COPY.
Do the 5,000 single inserts inside one transaction and time it again.
Use \copy to load a CSV from your machine. Then try COPY with the same path and read the error.
Insert an author and a book referring to it in one statement using WITH ... RETURNING.
Add an index to a table, insert 100,000 rows, then drop the index, truncate, and repeat. Compare.

Official documentation

PostgreSQL — INSERT — Every form, including RETURNING and ON CONFLICT.
PostgreSQL — COPY — The bulk loader, and the COPY versus \copy distinction.
PostgreSQL — Populating a database — The project's own advice on fast bulk loading, in order of effect.
PostgreSQL — Data-modifying statements in WITH — Inserting into two tables in one statement.

Next: UPDATE and DELETE, and the WHERE you must not forget.