INSERT INTO

Add new rows to a table with INSERT INTO, including multi-row inserts and conflict handling.

Overview

INSERT INTO adds new rows to a table. You can insert a single row, multiple rows in one statement, or the result of a SELECT query (INSERT ... SELECT). Explicitly listing column names in your INSERT is a best practice it makes the statement self-documenting and protects against future schema changes (adding a new column with a default will not break existing inserts). PostgreSQL extends standard INSERT with ON CONFLICT (commonly called "upsert") if the inserted row conflicts with an existing unique constraint, you can either do nothing or update the conflicting row. The RETURNING clause lets you retrieve the values of inserted rows including auto-generated IDs without a separate SELECT. For bulk loading large datasets, COPY is significantly faster than multi-row INSERT, but INSERT is the right tool for application-level data entry.

Key Points

•Always list column names explicitly: INSERT INTO t (col1, col2) VALUES (...)
•Multi-row insert: INSERT INTO t VALUES (r1), (r2), (r3) one round trip
•RETURNING col returns the value of any column after insert, including SERIAL ids
•ON CONFLICT DO NOTHING skips conflicting rows without error
•ON CONFLICT DO UPDATE (upsert) updates the existing row if a conflict occurs
•INSERT ... SELECT inserts the result of a query

Examples

Basic INSERT and multi-row INSERT

-- Single row
INSERT INTO users (email, username) VALUES ('alice@example.com', 'alice');

-- Multiple rows (one round trip faster)
INSERT INTO users (email, username) VALUES
  ('bob@example.com', 'bob'),
  ('carol@example.com', 'carol'),
  ('dave@example.com', 'dave');

RETURNING get the generated ID back

INSERT INTO orders (customer_id, total)
VALUES (42, 199.99)
RETURNING id, created_at;

RETURNING is PostgreSQL-specific and extremely useful for getting auto-generated values.

Upsert with ON CONFLICT

-- Insert or do nothing on duplicate email
INSERT INTO users (email, username)
VALUES ('alice@example.com', 'alice_new')
ON CONFLICT (email) DO NOTHING;

-- Insert or update on duplicate email
INSERT INTO users (email, username)
VALUES ('alice@example.com', 'alice_v2')
ON CONFLICT (email) DO UPDATE
  SET username = EXCLUDED.username,
      updated_at = NOW();

EXCLUDED refers to the row that was attempted to be inserted.

INSERT INTO

Overview

Key Points

Examples

Exercises

Knowledge Check

What does the RETURNING clause in an INSERT statement do?