INSERT, Properly

From lesson 1-1, the statement that adds a row to the pets table is:

INSERT INTO pets VALUES ('Rex', 'dog', 1);

INSERT INTO table VALUES (...) writes a new row, while SELECT only reads. You have been using INSERT since the very first lesson, and this one adds its safer and more flexible forms.

Naming your columns

Reading data was the first half of the course. But every signup, every message, every order your app handles arrives as an INSERT written by app code, and that code keeps running unchanged for years while the table evolves underneath it. That is why how you write an INSERT matters.

The bare INSERT INTO pets VALUES ('Rex', 'dog', 1) relies on you remembering the exact column order from CREATE TABLE. Add a column to the table later and every such INSERT silently puts values in the wrong place. The professional form names the columns:

INSERT INTO pets (name, species) VALUES ('Kiwi', 'bird');

Two benefits:

• Order no longer matters, you match values to the names you wrote.
• You can skip columns. Skipped ones become NULL (or a default, lesson 7-3). Kiwi's age is unknown, so we simply do not provide it.

You can also insert several rows in one statement by chaining value groups with commas.

A column list and a multi-row insert

Kiwi gets no age, which shows up as the empty spot after the last pipe, and the second statement adds two rows in one go.

CREATE TABLE pets (
  name TEXT,
  species TEXT,
  age INTEGER
);

INSERT INTO pets (name, species) VALUES ('Kiwi', 'bird');

INSERT INTO pets (name, species, age) VALUES
  ('Biscuit', 'dog', 3),
  ('Mochi', 'cat', 5);

SELECT * FROM pets;

Output

Kiwi|bird|
Biscuit|dog|3
Mochi|cat|5

The column list names which columns the values correspond to, so the order of the values follows the list rather than the table definition. That makes the statement immune to someone later adding a column in the middle of the table, which would silently break a bare VALUES insert.

A multi-row insert is also considerably faster than the same rows one statement at a time, because the database does its bookkeeping once instead of once per row.

Adding two pets in one statement

One INSERT, two value groups separated by a comma, with an explicit NULL for the age nobody knows.

CREATE TABLE pets (
  name TEXT,
  species TEXT,
  age INTEGER
);

INSERT INTO pets (name, species, age) VALUES
  ('Biscuit', 'dog', 3),
  ('Mochi', 'cat', 5);

INSERT INTO pets (name, species, age) VALUES
  ('Ziggy', 'parrot', 2),
  ('Rex', 'dog', NULL);

SELECT * FROM pets;

Output

Biscuit|dog|3
Mochi|cat|5
Ziggy|parrot|2
Rex|dog|

NULL is written without quotes. Writing 'NULL' with quotes would store the four-character text NULL instead, a genuinely confusing value that looks correct in a report and fails every IS NULL test.

Omitting age from the column list entirely would have the same effect for Rex, but it would need a second statement, since every value group in one INSERT must supply the same columns.

What a skipped column holds

For a table with name, species, and age, the statement INSERT INTO pets (name, species) VALUES ('Kiwi', 'bird'); stores NULL in Kiwi's age, or the column's DEFAULT value if one is declared.

Columns left out of the list are not errors. They simply take their default, which is NULL unless the table says otherwise, and lesson 7-3 covers declaring one.

That last row is the only case where skipping a column breaks anything, and it is the database doing its job.