How to INSERT Data in PostgreSQL

The INSERT statement adds new rows to a table. PostgreSQL extends standard SQL with useful features like RETURNING and ON CONFLICT for upserts.

Inserting a Single Row

INSERT INTO customers (first_name, last_name, email)
VALUES ('Alice', 'Johnson', 'alice@example.com');

List the target columns in parentheses, then provide values in the same order. Columns with defaults (like id or created_at) can be omitted.

Inserting Multiple Rows

INSERT INTO products (name, category, price)
VALUES
  ('Keyboard', 'Electronics', 49.99),
  ('Mouse', 'Electronics', 29.99),
  ('Desk Lamp', 'Office', 34.50);

Multi-row inserts are significantly faster than running separate INSERT statements because PostgreSQL processes them in a single transaction.

Using RETURNING

-- Get the generated ID back
INSERT INTO customers (first_name, last_name, email)
VALUES ('Bob', 'Smith', 'bob@example.com')
RETURNING id, created_at;

RETURNING is a PostgreSQL-specific feature that gives you back column values of the inserted row, eliminating the need for a separate SELECT query to fetch the generated ID.

Upsert with ON CONFLICT

-- Insert or update if email already exists
INSERT INTO customers (first_name, last_name, email)
VALUES ('Alice', 'Johnson', 'alice@example.com')
ON CONFLICT (email)
DO UPDATE SET
  first_name = EXCLUDED.first_name,
  last_name = EXCLUDED.last_name;

-- Insert or do nothing on conflict
INSERT INTO tags (name)
VALUES ('postgresql')
ON CONFLICT (name) DO NOTHING;

Common Use Cases

• Creating new user accounts from a registration form
• Bulk importing data from CSV or API responses
• Idempotent data syncing with ON CONFLICT upserts
• Inserting and immediately returning the generated ID for use in related tables