INSERT Multiple Rows | PostgreSQL Tutorial

PostgreSQL lets you insert multiple rows in one INSERT statement by supplying a comma-separated list of value tuples. This is more efficient than separate single-row inserts because it reduces round-trips to the server and wraps all inserts in a single atomic operation.

Multi-row INSERT syntax

Extend the standard INSERT syntax with additional value lists, each wrapped in parentheses and separated by commas:

INSERT INTO contacts (first_name, last_name, email)
VALUES
  ('John',    'Doe',     '[email protected]'),
  ('Jane',    'Smith',   '[email protected]'),
  ('Bob',     'Johnson', '[email protected]');

Output:

INSERT 0 3

The count in the command tag confirms how many rows were inserted. If any value list violates a constraint (for example, a duplicate UNIQUE email), the entire statement fails and no rows are inserted.

Returning inserted rows and IDs

Use the RETURNING clause to get back the inserted rows — useful when you need the auto-generated primary key for each row:

INSERT INTO contacts (first_name, last_name, email)
VALUES
  ('Alice',   'Johnson', '[email protected]'),
  ('Charlie', 'Brown',   '[email protected]')
RETURNING *;

Output:

 id | first_name | last_name |           email
----+------------+-----------+---------------------------
  4 | Alice      | Johnson   | [email protected]
  5 | Charlie    | Brown     | [email protected]
(2 rows)

To return only the generated IDs:

INSERT INTO contacts (first_name, last_name, email)
VALUES
  ('Eva',     'Williams', '[email protected]'),
  ('Michael', 'Miller',   '[email protected]'),
  ('Sophie',  'Davis',    '[email protected]')
RETURNING id;

Output:

 id
----
  6
  7
  8
(3 rows)

Practical tips

Batch inserts are atomic — if one value list violates a constraint, the whole statement rolls back. Use INSERT ... ON CONFLICT DO NOTHING if you want to skip conflicting rows instead of failing.
For very large datasets, COPY FROM is faster than even a single large multi-row INSERT because it bypasses per-row overhead.
Keep multi-row inserts inside explicit transactions when they are part of a larger workflow so you can roll back cleanly on application errors.
The order of rows returned by RETURNING matches the order of the VALUES lists, which lets you correlate returned IDs with your input data.

Reference: PostgreSQL documentation — INSERT.

Frequently Asked Questions

Is inserting multiple rows in one statement faster than separate inserts?

Yes. A single multi-row INSERT reduces the number of server round-trips and transaction commits. For large volumes, the difference is significant. For bulk loading of thousands of rows, COPY FROM is even faster.

What happens if one row in a multi-row INSERT violates a constraint?

The entire statement fails and no rows are inserted. If you want to skip conflicting rows, use INSERT ... ON CONFLICT DO NOTHING, which skips rows that would violate a unique or exclusion constraint.

Can I use RETURNING with a multi-row INSERT?

Yes. RETURNING works exactly the same as with a single-row INSERT and returns one result row for each inserted row. You can return specific columns or all columns with RETURNING *.

Is there a limit on how many rows I can insert at once?

PostgreSQL has no hard limit on the number of value lists in a single INSERT, but very large statements consume memory on both the client and server. For millions of rows, prefer COPY or break the inserts into smaller batches.

Does a multi-row INSERT guarantee the rows are committed in order?

The rows are inserted as a single atomic operation. The physical storage order is not guaranteed, but the RETURNING clause returns them in the order they were specified in the VALUES list.