Postgres by Example: INSERT

INSERT adds rows to a table. The basic shape is INSERT INTO table (columns) VALUES (values). You can insert one row, many rows in a single statement, or the result of a SELECT. Bulk insertion through a single INSERT ... VALUES (...), (...), ... is dramatically faster than thousands of separate statements because each statement otherwise pays a network round trip and a fresh planning cost.

What you'll learn:

• Single-row and multi-row INSERT
• Why naming columns explicitly is the right default
• Inserting from a query (INSERT INTO ... SELECT)
• Using DEFAULT and omitting columns
• Status messages: what INSERT 0 5 actually means

-- One row at a time (most explicit, often fine for app code)
INSERT INTO fruits (id, name) VALUES (1, 'apple');
INSERT INTO fruits (id, name) VALUES (2, 'banana');
INSERT INTO fruits (id, name) VALUES (3, 'cherry');

-- Many rows in one statement (much faster for bulk loads)
INSERT INTO fruits (id, name) VALUES
  (4, 'date'),
  (5, 'elderberry');

-- INSERT ... SELECT: copy rows from another query
-- (here a generator, to avoid duplicating fruits we just inserted)
INSERT INTO fruits (id, name)
SELECT n + 100, 'fruit_' || n
FROM generate_series(1, 3) AS n;

-- Confirm
SELECT count(*) AS row_count FROM fruits;

Run this script after create-table.sql — the table must exist. The first three statements each insert one row; the fourth inserts two; the fifth inserts three rows generated by generate_series. The status message INSERT 0 5 from psql means inserted, with old OID 0, 5 rows affected — OIDs are legacy and you can ignore the first number; the second is the count.

Why list the columns? Two reasons. First, if the table later gains a column, your INSERT INTO fruits VALUES (...) (no column list) will break, whereas INSERT INTO fruits (id, name) VALUES (...) keeps working. Second, listing columns makes the intent obvious to anyone reading the code — values are not positional puzzles.

For bulk loading from a file (CSV, etc.), prefer COPY (covered later) — it is significantly faster than INSERT. Application code should batch rows into multi-row INSERT statements or use a driver-level prepared statement.

To run:

$ psql -f source/insert.sql postgres
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 2
INSERT 0 3
 row_count
-----------
         8
(1 row)

Common pitfalls:

• Re-running the script when rows already exist causes primary key or unique violations (once you add a PK). The remedy depends on intent: drop and recreate the table, use TRUNCATE, or INSERT ... ON CONFLICT to upsert (next lesson).
• Wrong column order in a positional INSERT quietly corrupts data — the database happily inserts the wrong value into the wrong column if the types are compatible. Always list columns.
• Inserting timestamps with the wrong timezone is the usual cause of "the time is off by 8 hours" in production. Use timestamptz and let PostgreSQL handle the conversion.

Tip: INSERT INTO t DEFAULT VALUES; inserts a row using only column defaults — useful when every column has a DEFAULT (e.g. an id from a sequence and created_at from now()).

Try it: Add another row with (6, 'fig') and run the file again. (It will fail once id is unique — we will fix that in the upsert lesson.) Then try INSERT INTO fruits (id, name) SELECT n, 'gen_' || n FROM generate_series(200, 205) n; to see a query-driven insert.

Source: insert.sql

Next: SELECT from a Table

Home: Postgres by Example