PostgreSQL CRUD

How to Insert Multiple Rows in PostgreSQL (With Examples)

Insert multiple rows in PostgreSQL efficiently. Covers multi-value INSERT, INSERT SELECT, COPY, unnest, and RETURNING clause.

Mar 12, 2026 5 min read

Introduction

PostgreSQL offers several powerful methods for bulk inserts, including the ultra-fast COPY command and the unique RETURNING clause that gives you back the inserted data.

Insert Multiple Rows with VALUES

List multiple value tuples in a single INSERT statement.

INSERT INTO products (name, price, category)
VALUES
  ('Laptop', 999.99, 'Electronics'),
  ('Keyboard', 49.99, 'Electronics'),
  ('Desk', 299.99, 'Furniture'),
  ('Monitor', 399.99, 'Electronics'),
  ('Chair', 199.99, 'Furniture')
RETURNING id, name;  -- Get back the generated IDs!

Tip: RETURNING is a PostgreSQL superpower — it returns the inserted rows including generated columns like id and created_at.

INSERT with ON CONFLICT (Upsert)

Handle duplicates with ON CONFLICT — PostgreSQL's native upsert syntax.

INSERT INTO products (sku, name, price)
VALUES
  ('SKU001', 'Laptop', 999.99),
  ('SKU002', 'Keyboard', 49.99),
  ('SKU003', 'Monitor', 399.99)
ON CONFLICT (sku) DO UPDATE SET
  name = EXCLUDED.name,
  price = EXCLUDED.price
RETURNING *;

Tip: EXCLUDED refers to the row that would have been inserted. Combine with RETURNING to see what was inserted vs updated.

INSERT SELECT (Copy Between Tables)

Copy data from one table to another with optional transformation.

INSERT INTO active_users (id, name, email)
SELECT id, name, email
FROM users
WHERE last_login > NOW() - INTERVAL '30 days'
RETURNING id;

Tip: INSERT SELECT runs entirely server-side — no data leaves the database.

Bulk Insert with COPY (Fastest Method)

COPY is PostgreSQL's fastest data loading method. 10-100x faster than INSERT for large datasets.

-- Load from CSV (server-side):
COPY products (name, price, category)
FROM '/path/to/data.csv'
WITH (FORMAT csv, HEADER true);

-- Load from stdin (client-side, works with psql):
\copy products (name, price, category) FROM 'data.csv' WITH CSV HEADER

-- From application code (Python example):
-- cursor.copy_expert("COPY products FROM STDIN WITH CSV", file)

Tip: Use \copy (with backslash) from psql for client-side files. Use COPY (without backslash) for server-side files.

Insert with unnest (From Arrays)

Use unnest to insert data from PostgreSQL arrays — useful for application code that passes arrays.

-- Insert from arrays:
INSERT INTO users (name, email)
SELECT * FROM unnest(
  ARRAY['Alice', 'Bob', 'Charlie'],
  ARRAY['alice@example.com', 'bob@example.com', 'charlie@example.com']
)
RETURNING id, name, email;

Tip: unnest is great for parameterized batch inserts from application code — pass arrays instead of constructing multi-row VALUES.

Best Practices

Use COPY for loading large datasets (CSV, TSV files)
Always add RETURNING to get back generated IDs
Use ON CONFLICT for upsert patterns instead of check-then-insert
Wrap batch inserts in a transaction for atomicity
Use unnest for parameterized batch inserts from application code

Generate PostgreSQL Queries with AI2SQL

Skip the syntax lookup. Describe what you need in plain English and AI2SQL generates the correct PostgreSQL query instantly.

Try AI2SQL Free

No credit card required

Frequently Asked Questions

What is the fastest way to insert data into PostgreSQL?

The COPY command is the fastest — 10-100x faster than INSERT for large datasets. For programmatic inserts, use multi-row INSERT inside a transaction.

How does RETURNING work in PostgreSQL?

RETURNING appended to INSERT, UPDATE, or DELETE returns the affected rows. Example: INSERT INTO users (...) VALUES (...) RETURNING id; gives you the generated ID.

Can AI2SQL generate PostgreSQL insert queries?

Yes. Select PostgreSQL as your dialect and describe your insert operation. AI2SQL generates correct syntax including RETURNING and ON CONFLICT.