12. INSERT: adding rows

SELECT reads data; the next three lessons change it. We start with INSERT, which adds new rows to a table.

Every playground on this page starts with an empty product table (shown under Setup data below) so you can focus on the INSERT itself.

CREATE TABLE product (
  id       INTEGER PRIMARY KEY,
  name     VARCHAR NOT NULL,
  price    DECIMAL(6,2) NOT NULL,
  in_stock BOOLEAN NOT NULL DEFAULT true
);

Insert one row

Name the columns, then give matching values:

INSERT INTO product (id, name, price) VALUES (1, 'Notebook', 4.50);

SELECT * FROM product;

We left out in_stock, so its DEFAULT (true) filled in. Listing the columns you're setting — and letting defaults handle the rest — is the habit to build.

Insert several rows at once

One statement, several value lists. Faster than many separate inserts:

INSERT INTO product (id, name, price, in_stock) VALUES
  (1, 'Notebook', 4.50, true),
  (2, 'Pen',      1.25, true),
  (3, 'Backpack', 39.00, false);

SELECT * FROM product ORDER BY id;

Insert the result of a query

INSERT ... SELECT copies rows in from another query. Here we seed product from the first few pagila films — the SELECT just has to produce columns that line up with the insert:

INSERT INTO product (id, name, price)
SELECT film_id, title, rental_rate
FROM film
WHERE film_id <= 3;

SELECT * FROM product ORDER BY id;

Try it

Insert two rows into product: (10, 'Mug', 8.00) and (11, 'Lamp', 22.50) — both in stock by default — then select everything ordered by id.

-- insert the two rows, then select all ordered by id

INSERT INTO product (id, name, price) VALUES
  (10, 'Mug', 8.00),
  (11, 'Lamp', 22.50);
SELECT * FROM product ORDER BY id;