INSERT

INSERT adds new rows to a table. In its simplest form it names a table, lists which columns are being supplied, and provides the values for one or more rows. This page covers the everyday shapes of INSERT you will use constantly against the sample e-commerce schema, plus two habits worth building early: inserting from a query, and using DEFAULT explicitly.

Basic INSERT

Inserting a single customer

SQL

INSERT INTO customers (name, email)
VALUES ('Priya Nair', 'priya@example.com');

INSERT 0 1

The column list in parentheses tells PostgreSQL exactly which columns the following values line up with — any column left out (like customer_id, which is a SERIAL, or created_at, which has a DEFAULT of now()) is filled in automatically.

Multi-row INSERT

A single INSERT statement can carry several rows at once by listing multiple parenthesized value groups, separated by commas. This is both more concise and generally faster than issuing one INSERT per row, since it is a single round trip to the database.

Inserting several products at once

SQL

INSERT INTO products (sku, name, unit_price, stock_qty)
VALUES
  ('SKU-1001', 'Wireless Mouse', 24.99, 150),
  ('SKU-1002', 'Mechanical Keyboard', 89.50, 60),
  ('SKU-1003', 'USB-C Hub', 34.00, 200);

INSERT 0 3

INSERT ... SELECT

Instead of literal VALUES, INSERT can be fed the result of a SELECT query. This is how data gets copied between tables, transformed on the way in, or pulled from a staging table into a production one — anything a SELECT can produce, INSERT ... SELECT can load into a table.

Copying completed orders into an archive table

SQL

INSERT INTO orders_archive (order_id, customer_id, order_date, status)
SELECT order_id, customer_id, order_date, status
FROM orders
WHERE status = 'completed';

The DEFAULT keyword

DEFAULT can be used in place of a value to explicitly request a column's default, rather than simply omitting the column from the list. This is mostly useful in multi-row inserts or generated statements where every row needs a value in the same position, even if that value should just be "whatever the default is."

Explicitly requesting the default stock quantity

SQL

INSERT INTO products (sku, name, unit_price, stock_qty)
VALUES ('SKU-1004', 'Laptop Stand', 45.00, DEFAULT);

A quick look at RETURNING

Appending RETURNING to an INSERT hands back the row that was just created — most commonly used to grab an auto-generated id without a separate SELECT. The RETURNING page covers this in depth for INSERT, UPDATE, and DELETE alike.

Getting the new id back immediately

SQL

INSERT INTO customers (name, email)
VALUES ('Sam Okafor', 'sam@example.com')
RETURNING customer_id;

 customer_id
-------------
          42

Column order in VALUES must match the column list

Whatever order the columns are listed in after the table name, the VALUES tuple must supply values in that same order. If the column list is omitted entirely, PostgreSQL assumes every column of the table, in the order it was defined — which is more fragile, since adding a column later silently changes what "every column" means.

Prefer naming columns explicitly

Always writing out the column list, even when inserting into every column, protects existing INSERT statements from breaking silently if the table's column order changes later.

INSERT INTO table (columns) VALUES (...) adds one or more rows.
A single statement can insert multiple rows by listing several VALUES tuples.
INSERT ... SELECT loads rows produced by a query instead of literals.
DEFAULT can be used inline to explicitly request a column's default value.
RETURNING gets back the inserted row without a follow-up SELECT.