PostgreSQL INSERT

Basic syntax

INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...);

The column list is optional if you supply values for every column in table-definition order, but naming columns explicitly is safer and makes your queries resilient to schema changes.

Inserting a single row

INSERT INTO users (email, display_name, created_at)
VALUES ('alice@example.com', 'Alice Chen', now());

After the insert, SELECT * FROM users returns the new row. The id is assigned automatically by the sequence (or identity column in PostgreSQL).

Inserting multiple rows

You can insert multiple rows in a single statement by separating each row's values with a comma. This is faster than running individual INSERT statements because it requires only one round trip to the database.

INSERT INTO users (email, display_name)
VALUES
  ('alice@example.com', 'Alice Chen'),
  ('bob@example.com', 'Bob Okafor'),
  ('carol@example.com', 'Carol Santos');

All rows are inserted in a single atomic operation. If any row fails a constraint, the entire statement rolls back.

INSERT ... SELECT

Copy rows from one table into another using a SELECT statement as the source. This is useful for archiving data, populating staging tables, and duplicating rows with modifications.

INSERT INTO archived_users (id, email, display_name, archived_at)
SELECT id, email, display_name, now()
FROM users
WHERE last_login_at < now() - interval '365 days';

The column count and types in the SELECT output must match the target column list. There is no VALUES clause when using INSERT ... SELECT.

Returning inserted data with RETURNING

RETURNING gives you back column values from the rows that were just inserted. This avoids a second query to fetch auto-generated IDs or default values.

INSERT INTO users (email, display_name)
VALUES ('dana@example.com', 'Dana Kim')
RETURNING id, created_at;

You can return any column, including ones you did not supply in the INSERT:

INSERT INTO orders (user_id, total)
VALUES (42, 149.99)
RETURNING id, status, created_at;

RETURNING * returns all columns from the inserted row. This is a PostgreSQL extension and is not available in standard SQL.

ON CONFLICT (upsert)

ON CONFLICT handles the case where an insert would violate a unique constraint or primary key. You can either ignore the conflict or update the existing row.

DO NOTHING

Skip the insert without raising an error:

INSERT INTO users (email, display_name)
VALUES ('alice@example.com', 'Alice Chen')
ON CONFLICT (email) DO NOTHING;

The row is not inserted if a user with that email already exists. No error is raised, and any RETURNING clause returns no rows.

DO UPDATE (upsert)

Update the existing row when a conflict occurs:

INSERT INTO users (email, display_name, login_count)
VALUES ('alice@example.com', 'Alice Chen', 1)
ON CONFLICT (email) DO UPDATE
  SET display_name = EXCLUDED.display_name,
      login_count  = users.login_count + 1;

EXCLUDED refers to the row that was proposed for insertion. users.login_count refers to the value already in the table. This pattern is useful for tracking counts, updating cached values, and maintaining "insert or update" records.

You can add a WHERE clause to DO UPDATE to make the update conditional:

INSERT INTO products (sku, name, price)
VALUES ('WIDGET-001', 'Widget', 29.99)
ON CONFLICT (sku) DO UPDATE
  SET price = EXCLUDED.price
  WHERE EXCLUDED.price < products.price;

This only updates the price if the new value is lower than the current one.

INSERT with DEFAULT values

Omit a column from the column list to accept its DEFAULT. You can also write DEFAULT explicitly in the values list:

-- Both of these do the same thing when created_at has a default
INSERT INTO users (email, display_name)
VALUES ('evan@example.com', 'Evan Park');

INSERT INTO users (email, display_name, created_at)
VALUES ('evan@example.com', 'Evan Park', DEFAULT);

To insert a row composed entirely of default values (useful for tables where every column has a default):

INSERT INTO audit_log DEFAULT VALUES;

Common errors

Unique constraint violation

ERROR: duplicate key value violates unique constraint "users_email_key"
DETAIL: Key (email)=(alice@example.com) already exists.

A row with that value already exists in a column marked UNIQUE or as the primary key. Use ON CONFLICT DO NOTHING to skip duplicates, or ON CONFLICT DO UPDATE to update the existing row.

NOT NULL violation

ERROR: null value in column "email" of relation "users" violates not-null constraint
DETAIL: Failing row contains (5, null, Alice Chen, ...).

You either omitted a column that has no default and is marked NOT NULL, or passed NULL explicitly. Either supply a value, add a DEFAULT to the column definition, or make the column nullable if nulls are appropriate.

Type mismatch

ERROR: invalid input syntax for type integer: "abc"

The value you provided cannot be cast to the column's type. Check that string values are quoted, that numeric values contain no stray characters, and that dates are in a format PostgreSQL accepts ('2024-03-15' or ISO 8601).

Foreign key violation

ERROR: insert or update on table "orders" violates foreign key constraint "orders_user_id_fkey"
DETAIL: Key (user_id)=(999) is not present in table "users".

The value in the foreign key column does not match any row in the referenced table. Insert the parent row first, or verify the ID is correct.

Quick reference

| Syntax | Description | |--------|-------------| | INSERT INTO t (col) VALUES (val) | Insert a single row | | INSERT INTO t (col) VALUES (v1), (v2) | Insert multiple rows | | INSERT INTO t (col) SELECT col FROM other | Insert from a query | | INSERT INTO t ... RETURNING col | Return values from inserted rows | | INSERT INTO t ... ON CONFLICT (col) DO NOTHING | Skip on unique conflict | | INSERT INTO t ... ON CONFLICT (col) DO UPDATE SET ... | Upsert on unique conflict | | INSERT INTO t DEFAULT VALUES | Insert a row of all defaults |