INSERT

Adds new rows to a table, from literal values or from a query.

Syntax

INSERT INTO table (col1, col2, ...) VALUES (v1, v2, ...) [, (...)];
INSERT INTO table (col1, col2) SELECT ... FROM ...;
INSERT INTO table DEFAULT VALUES;
... [ON CONFLICT ...] [RETURNING ...]

Always list the target columns. Values are matched by position to that list; omitted columns get their DEFAULT (or NULL if none). Write DEFAULT explicitly to request the default for a listed column.
One statement can insert many rows: VALUES (...), (...), (...).
INSERT ... SELECT copies the result of a query (no VALUES keyword).
The statement is atomic: if any row violates a constraint (NOT NULL, UNIQUE, FOREIGN KEY, CHECK), no rows are inserted.
Identity / serial columns fill themselves; do not supply them unless you must (OVERRIDING SYSTEM VALUE for GENERATED ALWAYS).
RETURNING gives back the inserted rows (e.g. generated ids).

Examples

Insert rows (modifies data) starter

INSERT INTO customers (customer_id, customer_name, city, email, joined_on)
VALUES (101, 'Reem Al-Dosari', 'Riyadh', 'reem101@example.com', '2025-10-01'),
       (102, 'Yousef Kamal', 'Jeddah', NULL, current_date)
RETURNING *;

Two rows in one statement; RETURNING shows exactly what was stored.

Insert from a query (modifies data) starter

CREATE TABLE cancelled_orders_archive AS
SELECT * FROM orders WHERE false;

INSERT INTO cancelled_orders_archive
SELECT * FROM orders WHERE status = 'cancelled';

SELECT count(*) AS archived FROM cancelled_orders_archive;

CREATE TABLE AS ... WHERE false copies the column structure only; INSERT ... SELECT then fills it.

Pitfalls

Omitting the column list and relying on table column order.
Quoting numbers or forgetting quotes around text and dates.
Supplying a value for an identity primary key and then colliding with the sequence later.
Expecting partial success: one bad row aborts the whole statement.

Other databases

For orientation only: SQL Quest executes PostgreSQL.

SQL Server: OUTPUT inserted.* instead of RETURNING; IDENTITY columns need SET IDENTITY_INSERT ON to supply values.
MySQL: No RETURNING; use LAST_INSERT_ID() for an AUTO_INCREMENT id.
Oracle: Multi-row VALUES only since 23ai; earlier use INSERT ALL or INSERT ... SELECT ... FROM dual UNION ALL ...; RETURNING INTO in PL/SQL.