INSERT: Getting Data In

INSERT is how new rows get into a table. In PostgreSQL, the professional shape is INSERT INTO table_name (columns...) VALUES (...): name the target columns, give the row values, then verify what landed.

This is the first lesson where your SQL changes data. The playground is still safe: Run rebuilds the lesson's starting tables, then executes your script from the top. That makes this the right place to practice careful writing habits before the stakes get higher.

INSERT INTO VALUES: one row

The most direct insert names a table, names the columns you are supplying, and gives one row of values:

INSERT INTO daily_specials (name, category, price)
VALUES ('Lavender Latte', 'coffee', 4.80);
 
SELECT special_id, name, category, starts_on, price, active
FROM daily_specials
ORDER BY special_id;

INSERT 0 1
 special_id |      name      | category | starts_on  | price | active
------------+----------------+----------+------------+-------+--------
          1 | Lavender Latte | coffee   | 2026-05-01 |  4.80 | t
(1 row)

Read the command tag first: INSERT 0 1 means Postgres inserted one row. The 0 is historical bookkeeping that you can ignore in ordinary work; the final number is the useful count. Then read the verification query. The row has an identity id, a default starts_on date, and a default active value because the table supplies those when you do not.

Multi-row VALUES

You do not need one statement per row. Add more parenthesized rows after VALUES, separated by commas:

INSERT INTO daily_specials (name, category, price)
VALUES
  ('Miso Caramel Brownie', 'bakery', 4.20),
  ('Lemon Iced Tea', 'tea', 3.90);
 
SELECT count(*) AS specials
FROM daily_specials;

INSERT 0 2
 specials
----------
        3
(1 row)

The command tag says this statement inserted two rows. The count says the table now has three rows in this session: the first special, plus the two just added.

Multi-row VALUES is the normal spelling for a small batch you already have in your SQL script: seed rows, test rows, a short hand-entered list. Lesson 8.4 will cover real bulk loading from files; do not turn a CSV into thousands of handwritten VALUES rows.

Column lists are the contract

Always write the column list. It tells Postgres which value belongs to which column, and it protects your SQL from the table's physical column order.

Here the values arrive in price, name, category order. That is not the table's order, and it is still perfectly clear because the column list says what each value means:

INSERT INTO daily_specials (price, name, category)
VALUES (4.60, 'Honey Cardamom Bun', 'bakery');
 
SELECT special_id, name, category, price
FROM daily_specials
ORDER BY special_id;

INSERT 0 1
 special_id |         name         | category | price
------------+----------------------+----------+-------
          1 | Lavender Latte       | coffee   |  4.80
          2 | Miso Caramel Brownie | bakery   |  4.20
          3 | Lemon Iced Tea       | tea      |  3.90
          4 | Honey Cardamom Bun   | bakery   |  4.60
(4 rows)

Without a column list, values have to line up with every table column by position, including generated and defaulted columns. That is brittle code: a future schema change can move the target under your feet, and a reader has to remember the table definition just to understand the statement. Name the columns every time.

INSERT SELECT copies a query result

VALUES is for literal rows. When the rows already exist somewhere else, use INSERT ... SELECT: the SELECT produces a grid, and INSERT writes each result row into the target table.

This copies selected daily specials into an archive table and transforms the price into a label on the way in:

INSERT INTO special_archive (archived_name, category, price_band)
SELECT ds.name,
       ds.category,
       CASE
         WHEN ds.price >= 4.50 THEN 'premium'
         ELSE 'standard'
       END AS price_band
FROM daily_specials AS ds
WHERE ds.category IN ('coffee', 'bakery')
ORDER BY ds.special_id;
 
SELECT archive_id, archived_name, category, price_band
FROM special_archive
ORDER BY archive_id;

INSERT 0 3
 archive_id |    archived_name     | category | price_band
------------+----------------------+----------+------------
          1 | Lavender Latte       | coffee   | premium
          2 | Miso Caramel Brownie | bakery   | standard
          3 | Honey Cardamom Bun   | bakery   | premium
(3 rows)

The target column list has three columns, so the SELECT must return three expressions in compatible order. The names in the SELECT are for readability; the positions are what fill archived_name, category, and price_band.

That makes INSERT ... SELECT a bridge between the reading half of the course and the writing half. Every filtering, joining, grouping, CASE, subquery, CTE, and set-operation skill you learned can shape rows before they are inserted.

Defaults and DEFAULT VALUES

A default is a value the table can provide when your insert does not. You already saw defaults fill starts_on and active. You can also say DEFAULT explicitly for one column, or ask for a whole row made only from defaults:

INSERT INTO service_notes DEFAULT VALUES;
 
INSERT INTO service_notes (note_text, priority)
VALUES ('Check pastry case labels', DEFAULT);
 
SELECT note_id, note_text, created_on, priority
FROM service_notes
ORDER BY note_id;

INSERT 0 1
INSERT 0 1
 note_id |        note_text         | created_on | priority
---------+--------------------------+------------+----------
       1 | no note yet              | 2026-05-01 | normal
       2 | Check pastry case labels | 2026-05-01 | normal
(2 rows)

DEFAULT VALUES inserts exactly one row, and every column must either have a default, generate itself, or allow NULL. The second insert supplies note_text and asks the table to use the default priority. That is different from an empty string or a made-up placeholder: the database owns the default, so every writer gets the same rule.

Try the whole flow here. Watch the command tags after each insert, then change one literal and Run again to see the reset safety in action.

You can now create new rows deliberately: literal rows with VALUES, copied rows with INSERT ... SELECT, and default-filled rows when the table owns the missing pieces. Next lesson keeps the same discipline but raises the risk: changing and removing rows with UPDATE and DELETE, always by rehearsing the target first.