INSERT Multiple Rows in PostgreSQL

PostgreSQL lets you insert multiple rows in a single INSERT statement by supplying a comma-separated list of value tuples after VALUES. This is more efficient than issuing separate single-row inserts: it reduces round-trips to the server, and the entire batch executes as one atomic operation — either all rows are inserted or none are.

Syntax

INSERT INTO table_name (column1, column2, ...)
VALUES
  (row1_val1, row1_val2, ...),
  (row2_val1, row2_val2, ...),
  (row3_val1, row3_val2, ...);

With RETURNING to retrieve generated primary keys:

INSERT INTO table_name (column1, column2)
VALUES
  (val1a, val1b),
  (val2a, val2b)
RETURNING id;

Practical Example

Create an events table and a registrations table, then insert multiple registrations in one statement:

CREATE TABLE events (
  id         SERIAL PRIMARY KEY,
  title      VARCHAR(200) NOT NULL,
  venue      VARCHAR(200),
  starts_at  TIMESTAMPTZ NOT NULL
);

CREATE TABLE registrations (
  id         SERIAL PRIMARY KEY,
  event_id   INT NOT NULL REFERENCES events(id),
  attendee   VARCHAR(150) NOT NULL,
  email      VARCHAR(255) NOT NULL,
  registered_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

INSERT INTO events (title, venue, starts_at)
VALUES ('Cloud Infrastructure Summit', 'Convention Hall A', '2026-06-15 09:00:00+00');

Insert several registrations for event id 1 in a single statement:

INSERT INTO registrations (event_id, attendee, email)
VALUES
  (1, 'Jordan Lee',    'jordan.lee@example'),
  (1, 'Sam Rivera',    'sam.rivera@example'),
  (1, 'Casey Nguyen',  'casey.nguyen@example'),
  (1, 'Avery Patel',   'avery.patel@example');

Output:

INSERT 0 4

The count in the command tag confirms all four rows were inserted. Verify with a query:

SELECT id, attendee, email FROM registrations ORDER BY id;

RETURNING with Multi-Row INSERT

Use RETURNING to retrieve the auto-generated ID for each inserted row in the same statement — no follow-up SELECT needed:

INSERT INTO registrations (event_id, attendee, email)
VALUES
  (1, 'Morgan Kim',    'morgan.kim@example'),
  (1, 'Dakota Torres', 'dakota.torres@example')
RETURNING id, attendee;

 id |   attendee
----+--------------
  5 | Morgan Kim
  6 | Dakota Torres
(2 rows)

The rows in the RETURNING result appear in the same order as the VALUES lists, making it straightforward to correlate returned IDs with your input data.

Testing with Vela

Multi-row inserts are a common step in data migrations and seed scripts. Before running them against a production database, use Vela database branching to create a branch from your production snapshot. Run the batch INSERT on the branch, inspect the returned IDs, and verify referential integrity — then apply to production only when you’re confident the data is correct. Branches are instant and free you from maintaining a separate staging environment.

Production Tips

• Specify an explicit column list so the statement remains correct if columns are added to the table later.
• The batch is atomic — if any row violates a constraint, the entire insert rolls back. Use ON CONFLICT DO NOTHING to skip conflicting rows without failing the whole statement.
• Use RETURNING id (or RETURNING *) to correlate inserted rows with application-side data structures without issuing a second query.
• For very large datasets (thousands of rows), COPY FROM is significantly faster than even a single large multi-row INSERT.
• Keep batch sizes manageable (a few hundred to a few thousand rows per statement) to avoid excessive memory pressure on the server.
• Wrap multi-row inserts that are part of a larger workflow in an explicit transaction so you can roll back on application errors.