INSERT in PostgreSQL

The INSERT statement is the standard way to add rows to a PostgreSQL table. You name the table, list the columns you want to populate, and supply matching values. PostgreSQL inserts exactly one row per VALUES list and returns a command tag (INSERT 0 1) confirming the row count.

Syntax

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

Retrieve the auto-generated primary key with RETURNING:

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

Practical Example

Create an orders table and insert a row:

CREATE TABLE orders (
  id          SERIAL PRIMARY KEY,
  customer    VARCHAR(100) NOT NULL,
  product_sku VARCHAR(50)  NOT NULL,
  quantity    INT          NOT NULL DEFAULT 1,
  unit_price  NUMERIC(10, 2) NOT NULL,
  created_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);

INSERT INTO orders (customer, product_sku, quantity, unit_price)
VALUES ('Acme Corp', 'SKU-7721', 3, 49.99);

Output:

INSERT 0 1

To insert a string containing a single quote, double the quote character:

INSERT INTO orders (customer, product_sku, quantity, unit_price)
VALUES ('O''Brien LLC', 'SKU-1002', 1, 199.00);

Use RETURNING to get the generated id and creation timestamp in the same statement:

INSERT INTO orders (customer, product_sku, quantity, unit_price)
VALUES ('Northgate Inc', 'SKU-4450', 5, 25.00)
RETURNING id, created_at;

 id |          created_at
----+-------------------------------
  3 | 2026-04-07 09:15:42.123456+00
(1 row)

Inspect the table to confirm all rows:

SELECT id, customer, product_sku, quantity, unit_price
FROM orders
ORDER BY id;

RETURNING Clause

RETURNING is a PostgreSQL extension to standard SQL. It returns column values from the newly inserted row — useful for capturing auto-generated primary keys, timestamps, or computed defaults without issuing a second query.

INSERT INTO orders (customer, product_sku, quantity, unit_price)
VALUES ('Eastwick Co', 'SKU-9910', 2, 75.50)
RETURNING id AS new_order_id, created_at AS order_time;

You can return every column with RETURNING *, or rename individual columns with AS. The value is read from the row after all defaults and triggers have been applied.

Use a data-modifying CTE to chain the inserted row into a follow-up operation within the same transaction:

WITH new_order AS (
  INSERT INTO orders (customer, product_sku, quantity, unit_price)
  VALUES ('Harbor Trades', 'SKU-3310', 10, 12.00)
  RETURNING id
)
INSERT INTO order_audit (order_id, event, recorded_at)
SELECT id, 'created', now()
FROM new_order;

Testing with Vela

Before running INSERT-heavy migrations or bulk data loads in production, use Vela database branching to create an instant copy-on-write clone of your production database. Test the INSERT statements — including RETURNING logic and any trigger side-effects — on the branch, then promote to production only when the results are confirmed. This is especially valuable when inserting into tables with complex constraints or referential integrity rules.

Production Tips

• Always specify an explicit column list. Relying on column position breaks silently when columns are added or reordered.
• Use RETURNING id instead of a follow-up SELECT to retrieve the generated primary key — it is atomic and saves a round-trip.
• Wrap related inserts in an explicit transaction so all succeed together or none are persisted.
• For bulk data loads, COPY FROM is significantly faster than many individual INSERT statements.
• Use INSERT ... ON CONFLICT DO NOTHING to skip rows that would violate a unique constraint rather than catching errors in application code.
• SERIAL and IDENTITY columns generate values automatically — omit them from the column list and let PostgreSQL handle them.