INSERT, defaults and getting generated keys

A shop that signs up 3 customers in one statement still needs each new customer’s number before it can store their first orders, and asking the table for its largest number afterwards can return someone else’s. INSERT adds rows: after the table’s name you list, in parentheses, the columns you give values for, and after VALUES one list of values per new row, in the same order. A column you leave out gets its defaultcolumn default: The value a column's DEFAULT clause gives a new row when the INSERT leaves that column out of its column list. An explicit NULL is a value, so it never brings the default in., or NULL when it has none; INSERT … SELECT adds the rows a query returns; and the key the database generatesgenerated key: A key the database chooses for a new row, from an identity column or sequence, an AUTO_INCREMENT counter or SQLite's rowid. It is unique, but it is not a count of rows and it can have gaps. comes back from the INSERT itself, with a RETURNINGRETURNING: A clause that makes an INSERT, UPDATE or DELETE return the rows it changed, with the columns it names, in the same round trip. PostgreSQL and SQLite have it; MySQL does not. clause in PostgreSQL and SQLite and with LAST_INSERT_ID() in MySQL.

The scripts on this page add customers and products to mart, the practice shop of this track, and sql.js 1.14.2, a browser build of SQLite 3.49.1, executes them. Two programs near the end run the same statements on PostgreSQL 18.6 and MySQL 8.4.11 and show, as each server printed it, where those engines differ.

One INSERT adds one row or many, matched to its column list

Asha Kulkarni joins mart first, then three customers arrive at once. Press Run and read the table the script prints:

-- mart's customers table, with two columns that have a default.
CREATE TABLE customers (
  customer_id  INTEGER PRIMARY KEY,
  name         TEXT    NOT NULL,
  city         TEXT    NOT NULL,
  plan         TEXT    NOT NULL DEFAULT 'basic',
  credit_paise INTEGER NOT NULL DEFAULT 0      -- 100 paise = 1 rupee
);

-- One row: name the columns, then give one value per column, in the same order.
INSERT INTO customers (customer_id, name, city)
VALUES (1, 'Asha Kulkarni', 'Pune');

-- Three rows in one statement: one list of values per row.
INSERT INTO customers (customer_id, name, city, plan)
VALUES (2, 'Vikram Nair',   'Kochi',   'plus'),
       (3, 'Meera Rathore', 'Jaipur',  'basic'),
       (4, 'Tenzin Dorjee', 'Gangtok', 'plus');

SELECT * FROM customers;

┌─────────────┬───────────────┬─────────┬───────┬──────────────┐
│ customer_id │     name      │  city   │ plan  │ credit_paise │
├─────────────┼───────────────┼─────────┼───────┼──────────────┤
│ 1           │ Asha Kulkarni │ Pune    │ basic │ 0            │
│ 2           │ Vikram Nair   │ Kochi   │ plus  │ 0            │
│ 3           │ Meera Rathore │ Jaipur  │ basic │ 0            │
│ 4           │ Tenzin Dorjee │ Gangtok │ plus  │ 0            │
└─────────────┴───────────────┴─────────┴───────┴──────────────┘

The first INSERT names three columns and gives three values, matched one by one: 1 goes to customer_id, 'Asha Kulkarni' to name, 'Pune' to city. Text is written in single quotes and numbers without them. The second INSERT adds three rows at once, one parenthesised list per row, separated by commas. All three engines accept such a multi-row VALUES list, and it is faster than three separate statements, because the database parses one statement and the program waits for one answer instead of three. The two columns that no INSERT named, plan for Asha and credit_paise for everyone, got their defaults: 'basic' and 0.

Each value of a new row comes from one of three places: the statement’s own list, a column’s default, or the database’s key generator. The diagram follows one statement that uses all three:

The column list protects the statement from table changes

SQL lets you leave the column list out. The values must then cover every column of the table, in the order the table defines them. That works until the table changes:

-- An INSERT without a column list depends on the table's column order.
CREATE TABLE customers (
  customer_id INTEGER PRIMARY KEY,
  name        TEXT    NOT NULL,
  city        TEXT    NOT NULL
);
INSERT INTO customers VALUES (1, 'Asha Kulkarni', 'Pune');   -- works today

-- A migration adds a column, and the same kind of statement stops working.
ALTER TABLE customers ADD COLUMN phone TEXT;
INSERT INTO customers VALUES (2, 'Vikram Nair', 'Kochi');

-- With a column list, it keeps working: phone is left out and stays NULL.
INSERT INTO customers (customer_id, name, city) VALUES (2, 'Vikram Nair', 'Kochi');
SELECT * FROM customers;

-- Worse: a rebuilt table lists city before name. Nothing fails, and the data is wrong.
CREATE TABLE customers_v2 (
  customer_id INTEGER PRIMARY KEY,
  city        TEXT    NOT NULL,
  name        TEXT    NOT NULL
);
INSERT INTO customers_v2 VALUES (3, 'Meera Rathore', 'Jaipur');
SELECT customer_id, name, city FROM customers_v2;

┌─────────────┬───────────────┬───────┬───────┐
│ customer_id │     name      │ city  │ phone │
├─────────────┼───────────────┼───────┼───────┤
│ 1           │ Asha Kulkarni │ Pune  │       │
│ 2           │ Vikram Nair   │ Kochi │       │
└─────────────┴───────────────┴───────┴───────┘
┌─────────────┬────────┬───────────────┐
│ customer_id │  name  │     city      │
├─────────────┼────────┼───────────────┤
│ 3           │ Jaipur │ Meera Rathore │
└─────────────┴────────┴───────────────┘

Parse error near line 11: table customers has 4 columns but 3 values were supplied

Step through what the short form did, value by value, before the migration, after it and on the rebuilt table:

After the migration added phone, the short form failed: four columns, three values. That failure is the lucky case, because someone notices it. The rebuilt table shows the unlucky one. Its columns come in a different order, both are text, and the same kind of statement put the city into name and the name into city without a word.

One refused row undoes the whole statement

Three new customers arrive in one statement, and the third has no city:

CREATE TABLE customers (
  customer_id INTEGER PRIMARY KEY,
  name        TEXT    NOT NULL,
  city        TEXT    NOT NULL
);
INSERT INTO customers (customer_id, name, city) VALUES (1, 'Asha Kulkarni', 'Pune');

-- Three new rows; the last one has no city.
INSERT INTO customers (customer_id, name, city)
VALUES (2, 'Vikram Nair',   'Kochi'),
       (3, 'Meera Rathore', 'Jaipur'),
       (4, 'Tenzin Dorjee', NULL);

-- The statement failed as a whole: the two good rows were taken back too.
SELECT count(*) AS customers FROM customers;

-- Asking to keep the good rows: SQLite's INSERT OR IGNORE skips the row that breaks a rule.
INSERT OR IGNORE INTO customers (customer_id, name, city)
VALUES (2, 'Vikram Nair',   'Kochi'),
       (3, 'Meera Rathore', 'Jaipur'),
       (4, 'Tenzin Dorjee', NULL);
SELECT customer_id, name, city FROM customers;

┌───────────┐
│ customers │
├───────────┤
│ 1         │
└───────────┘
┌─────────────┬───────────────┬────────┐
│ customer_id │     name      │  city  │
├─────────────┼───────────────┼────────┤
│ 1           │ Asha Kulkarni │ Pune   │
│ 2           │ Vikram Nair   │ Kochi  │
│ 3           │ Meera Rathore │ Jaipur │
└─────────────┴───────────────┴────────┘

Runtime error near line 9: NOT NULL constraint failed: customers.city

After the failed statement the table still holds one customer. Step through it to see where the first two new rows went:

A statement succeeds or fails as a whole: when one row of a multi-row INSERT is refused, the rows before it are taken back too. PostgreSQL and MySQL’s default InnoDB tables behave the same way.

To keep the good rows, a statement has to ask for it, and the two ways to ask do different things. SQLite’s INSERT OR IGNORE skips each row that breaks a NOT NULL, UNIQUE, CHECK or primary key rule, without an error or a warning: at the end of the script it added Vikram Nair and Meera Rathore and left Tenzin Dorjee out. MySQL’s INSERT IGNORE turns the errors into warnings, and a NULL in a NOT NULL column is not skipped at all: MySQL stores the implicit default of the column’s type instead, so the MySQL run near the end of this lesson keeps Tenzin Dorjee with an empty city. For loading data, checking the rows first and inserting the good ones is usually the better plan, because the rows left out can then be listed and fixed.

A column left out gets its default; NULL is a value

mart’s site records page visits, and every column but the key has a default. The script tries five ways of leaving a value to the database:

-- Page visits of a shop's website: every column but the key has a default.
CREATE TABLE visits (
  visit_id INTEGER PRIMARY KEY,
  page     TEXT    NOT NULL DEFAULT '/',
  device   TEXT             DEFAULT 'phone',
  seconds  INTEGER NOT NULL DEFAULT 0
);

-- 1. Columns left out of the list get their default.
INSERT INTO visits (page) VALUES ('/learn/sql/');

-- 2. A row made of defaults only.
INSERT INTO visits DEFAULT VALUES;

-- 3. An explicit NULL is a value of its own: it replaces the default.
INSERT INTO visits (page, device) VALUES ('/tools/', NULL);

-- 4. A NOT NULL column refuses NULL, even when it has a default.
INSERT INTO visits (page, seconds) VALUES ('/about/', NULL);

-- 5. PostgreSQL and MySQL accept the keyword DEFAULT in a VALUES list; SQLite does not.
INSERT INTO visits (page, device) VALUES ('/blog/', DEFAULT);

SELECT * FROM visits;

┌──────────┬─────────────┬────────┬─────────┐
│ visit_id │    page     │ device │ seconds │
├──────────┼─────────────┼────────┼─────────┤
│ 1        │ /learn/sql/ │ phone  │ 0       │
│ 2        │ /           │ phone  │ 0       │
│ 3        │ /tools/     │        │ 0       │
└──────────┴─────────────┴────────┴─────────┘

Runtime error near line 19: NOT NULL constraint failed: visits.seconds
Parse error near line 22: near "DEFAULT": syntax error

Step through the five statements and watch where each column of the new row gets its value:

Statement by statement:

1. page was given; device and seconds were left out and got their defaults, 'phone' and 0.
2. DEFAULT VALUES takes the default of every column. visit_id has none, so SQLite numbered the row itself.
3. An explicit NULL is a value. It replaced the default, and device is empty on that row.
4. seconds is NOT NULL, so NULL is refused there, default or not.
5. PostgreSQL and MySQL accept the keyword DEFAULT in place of a value, as in VALUES ('/blog/', DEFAULT). SQLite reports a syntax error; leave the column out of the list instead.

MySQL has no DEFAULT VALUES. There a row of defaults is INSERT INTO visits () VALUES (): an empty column list and an empty list of values. A default does not have to be a constant. DEFAULT CURRENT_TIMESTAMP, for example, is worked out for each row at the moment it is inserted. The rule that catches people is the third statement: a program that always sends every column, and sends NULL for “no value”, never sees a default at all.

INSERT with SELECT copies a query’s rows by position

mart keeps a price history: once a month, the price of every product still on sale is copied into a second table.

-- mart's products, and a history table that keeps each product's price per month.
CREATE TABLE products (
  product_id  INTEGER PRIMARY KEY,
  name        TEXT    NOT NULL,
  price_paise INTEGER NOT NULL,
  active      INTEGER NOT NULL DEFAULT 1        -- 0 = no longer sold
);
INSERT INTO products (product_id, name, price_paise, active)
VALUES (1, 'Basmati rice 1 kg', 14999, 1),
       (2, 'Toor dal 500 g',     8950, 1),
       (3, 'Masala chai 250 g', 12000, 1),
       (4, 'Coconut oil 1 L',   21500, 0),
       (5, 'Ragi flour 1 kg',    6800, 1);

CREATE TABLE price_history (
  product_id  INTEGER NOT NULL REFERENCES products (product_id),
  month       TEXT    NOT NULL,                 -- 'YYYY-MM'
  price_paise INTEGER NOT NULL,
  PRIMARY KEY (product_id, month)
);

-- One statement copies this month's price of every product still sold.
-- The SELECT list fills the column list by position: product_id, month, price_paise.
INSERT INTO price_history (product_id, month, price_paise)
SELECT product_id, '2026-10', price_paise
FROM products
WHERE active = 1;

SELECT changes() AS rows_copied;
SELECT * FROM price_history;

-- The same copy run a second time breaks the primary key, and copies nothing.
INSERT INTO price_history (product_id, month, price_paise)
SELECT product_id, '2026-10', price_paise FROM products WHERE active = 1;
SELECT count(*) AS rows_now FROM price_history;

┌─────────────┐
│ rows_copied │
├─────────────┤
│ 4           │
└─────────────┘
┌────────────┬─────────┬─────────────┐
│ product_id │  month  │ price_paise │
├────────────┼─────────┼─────────────┤
│ 1          │ 2026-10 │ 14999       │
│ 2          │ 2026-10 │ 8950        │
│ 3          │ 2026-10 │ 12000       │
│ 5          │ 2026-10 │ 6800        │
└────────────┴─────────┴─────────────┘
┌──────────┐
│ rows_now │
├──────────┤
│ 4        │
└──────────┘

Runtime error near line 33: UNIQUE constraint failed: price_history.product_id, price_history.month

The SELECT returned four rows, the inactive coconut oil left out, and each became a row of price_history. The month is a constant in the SELECT list, which is how you give every copied row the same value. Step through how the copy pairs the query’s values with the column list, and what a swap of two items does:

The SELECT’s columns fill the column list by position, not by name: the first goes to product_id, the second to month, the third to price_paise, whatever they are called. Only a constraint can catch a swap, such as an enforced foreign key that finds no product numbered 14999.

Running the copy a second time failed on the primary key, and, as before, it copied nothing at all. A copy that may run twice, such as a monthly job that is retried, needs an insert that skips or updates the rows that already exist: an upsert, the subject of a later lesson of this module. INSERT … SELECT needs a table that exists. CREATE TABLE … AS SELECT makes a new table from a query instead, with the query’s columns; in SQLite such a table has no primary key and no constraints of any kind.

The new key comes back from the INSERT itself

Most tables let the database choose the key of a new row:

• SQLite numbers a row whose INTEGER PRIMARY KEY is left out: by default the largest key in the table plus one. With AUTOINCREMENT it never hands out a number twice.
• PostgreSQL declares GENERATED ALWAYS AS IDENTITY (or the older serial), and a sequenceA database object that hands out increasing numbers, one at a time. PostgreSQL's identity columns take their keys from one, and a number it has handed out is never given back, even when the INSERT fails. gives out the numbers.
• MySQL declares AUTO_INCREMENT, and InnoDB keeps a counter for the table.

The program that inserted the row needs the new key straight away, for example to insert the items of an order it has just created. Asking the table afterwards is the trap. Step through two sign-ups that arrive at the same moment on two connections:

Between request A’s INSERT and its SELECT max(customer_id), request B added a customer of its own, so A read B’s key, and no error was raised anywhere. The key has to come from the INSERT itself:

-- Two customers exist; the database numbers the next ones itself.
CREATE TABLE customers (
  customer_id INTEGER PRIMARY KEY,
  name        TEXT    NOT NULL,
  city        TEXT    NOT NULL,
  plan        TEXT    NOT NULL DEFAULT 'basic'
);
INSERT INTO customers (customer_id, name, city)
VALUES (1, 'Asha Kulkarni', 'Pune'), (2, 'Vikram Nair', 'Kochi');

-- No customer_id given: SQLite chooses it, and RETURNING sends the new row back.
INSERT INTO customers (name, city) VALUES ('Farhan Qureshi', 'Lucknow')
RETURNING customer_id, name, plan;

-- One RETURNING row for each row inserted.
INSERT INTO customers (name, city)
VALUES ('Lalremruati', 'Aizawl'), ('Gurpreet Kaur', 'Amritsar')
RETURNING customer_id, name;

-- The connection also keeps the last new rowid and the rows the last statement changed.
SELECT last_insert_rowid() AS last_id, changes() AS rows_changed;

┌─────────────┬────────────────┬───────┐
│ customer_id │      name      │ plan  │
├─────────────┼────────────────┼───────┤
│ 3           │ Farhan Qureshi │ basic │
└─────────────┴────────────────┴───────┘
┌─────────────┬───────────────┐
│ customer_id │     name      │
├─────────────┼───────────────┤
│ 4           │ Lalremruati   │
│ 5           │ Gurpreet Kaur │
└─────────────┴───────────────┘
┌─────────┬──────────────┐
│ last_id │ rows_changed │
├─────────┼──────────────┤
│ 5       │ 2            │
└─────────┴──────────────┘

RETURNING turns an INSERT into a statement that also returns rows: one per inserted row, with the columns you ask for. Values the database filled in, the key and the default plan, come back with the rest, in the same round trip. SQLite has had RETURNING since version 3.35, and PostgreSQL has it on INSERT, UPDATE, DELETE and MERGE. Do not rely on the order of the returned rows: SQLite’s manual calls it arbitrary. When the order matters, return a column that identifies each row, such as its name. SQLite also keeps two numbers per connection: last_insert_rowid(), the key of the last row inserted, and changes(), how many rows the last statement inserted, changed or deleted.

MySQL has no RETURNING; INSERT … RETURNING customer_id is a syntax error there. After the INSERT, the same connection runs SELECT LAST_INSERT_ID(). Two details matter:

• The value belongs to the connection. Rows other connections insert in the meantime never change it, so it is safe under load.
• After a multi-row INSERT, it returns the key of the first row of the statement, not the last one, and ROW_COUNT() tells how many rows were added.

In PostgreSQL, RETURNING is the usual answer; the functions lastval() and currval() also give the last number a sequence handed out in your session.

Generated keys are unique, not consecutive

Generated keys are unique, and that is all they promise: they are not a count of rows, and they are not guaranteed to be consecutive. Two tables of support tickets lose their newest ticket, and each gets a new one:

-- Two tables of support tickets, one with AUTOINCREMENT and one without.
CREATE TABLE tickets      (ticket_id INTEGER PRIMARY KEY,               subject TEXT NOT NULL);
CREATE TABLE tickets_auto (ticket_id INTEGER PRIMARY KEY AUTOINCREMENT, subject TEXT NOT NULL);

INSERT INTO tickets      (subject) VALUES ('Refund'), ('Late delivery'), ('Wrong item');
INSERT INTO tickets_auto (subject) VALUES ('Refund'), ('Late delivery'), ('Wrong item');

-- Ticket 3, the newest, is deleted in both tables.
DELETE FROM tickets      WHERE ticket_id = 3;
DELETE FROM tickets_auto WHERE ticket_id = 3;

-- The next ticket: largest key + 1, or a number never used before.
INSERT INTO tickets      (subject) VALUES ('Damaged box') RETURNING ticket_id AS plain_key;
INSERT INTO tickets_auto (subject) VALUES ('Damaged box') RETURNING ticket_id AS autoincrement_key;

┌───────────┐
│ plain_key │
├───────────┤
│ 3         │
└───────────┘
┌───────────────────┐
│ autoincrement_key │
├───────────────────┤
│ 4                 │
└───────────────────┘

Without AUTOINCREMENT, SQLite gave the next ticket the number 3 again, because after the delete the largest key was 2. A link or an email that still names “ticket 3” now points at a different ticket. With AUTOINCREMENT, the next number was 4.

PostgreSQL’s sequences and MySQL’s counters only move forward, so they leave gaps instead: a number taken by an INSERT that failed or was rolled back is not given out again. In the PostgreSQL run of the next section, a rolled-back insert took 4, a failed two-row insert took 5 and 6, and the next customer got 7. Treat a gap as normal, and never as a sign that a row went missing.

PostgreSQL, MySQL and SQLite differ in the details

Every statement of this lesson runs in all three engines, but several details are spelt differently. Read across a row to see where one engine’s habit breaks in another:

Text that is too long for its column, or a word in a number column, follows the type rules of the lessons on data types: PostgreSQL refuses it, MySQL refuses it in its default strict mode (errors 1406 and 1366), and an ordinary SQLite table stores it as it came, unless the table is declared STRICT.

The two programs below run statements of this lesson on real servers, PostgreSQL 18.6 and MySQL 8.4.11, each started for the run on this computer and deleted after it. The first is PostgreSQL’s:

"""PostgreSQL 18.6: RETURNING with an identity key, the keyword DEFAULT, a key given by hand, and the gaps that a
rolled-back and a failed INSERT leave in the numbers."""

from pg_server import psql

psql(r"""
CREATE TABLE customers (
  customer_id int  GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name        text NOT NULL,
  city        text NOT NULL,
  plan        text NOT NULL DEFAULT 'basic'
);

\echo '-- 1. RETURNING sends back the keys the identity column made, and the default plan'
INSERT INTO customers (name, city) VALUES ('Asha Kulkarni', 'Pune'), ('Vikram Nair', 'Kochi')
RETURNING customer_id, name, plan;

\echo '-- 2. The keyword DEFAULT asks for a column default by name'
INSERT INTO customers (name, city, plan) VALUES ('Farhan Qureshi', 'Lucknow', DEFAULT)
RETURNING customer_id, plan;

\echo '-- 3. GENERATED ALWAYS refuses a key given by hand'
INSERT INTO customers (customer_id, name, city) VALUES (10, 'Meera Rathore', 'Jaipur');

\echo '-- 4. A rolled-back INSERT, then a failed one'
BEGIN;
INSERT INTO customers (name, city) VALUES ('Tenzin Dorjee', 'Gangtok') RETURNING customer_id;
ROLLBACK;
INSERT INTO customers (name, city) VALUES ('Lalremruati', 'Aizawl'), ('Gurpreet Kaur', NULL);

\echo '-- 5. The next INSERT, and the keys in the table'
INSERT INTO customers (name, city) VALUES ('Lalremruati', 'Aizawl') RETURNING customer_id;
SELECT customer_id, name FROM customers ORDER BY customer_id;
""")

-- 1. RETURNING sends back the keys the identity column made, and the default plan
 customer_id |     name      | plan  
-------------+---------------+-------
           1 | Asha Kulkarni | basic
           2 | Vikram Nair   | basic
(2 rows)

-- 2. The keyword DEFAULT asks for a column default by name
 customer_id | plan  
-------------+-------
           3 | basic
(1 row)

-- 3. GENERATED ALWAYS refuses a key given by hand
ERROR:  cannot insert a non-DEFAULT value into column "customer_id"
DETAIL:  Column "customer_id" is an identity column defined as GENERATED ALWAYS.
HINT:  Use OVERRIDING SYSTEM VALUE to override.
-- 4. A rolled-back INSERT, then a failed one
 customer_id 
-------------
           4
(1 row)

ERROR:  null value in column "city" of relation "customers" violates not-null constraint
DETAIL:  Failing row contains (6, Gurpreet Kaur, null, basic).
-- 5. The next INSERT, and the keys in the table
 customer_id 
-------------
           7
(1 row)

 customer_id |      name      
-------------+----------------
           1 | Asha Kulkarni
           2 | Vikram Nair
           3 | Farhan Qureshi
           7 | Lalremruati
(4 rows)

"""Runs SQL on a private, throwaway PostgreSQL 18.6 server and prints what psql printed.

Each run makes a new database cluster in its own temporary folder (initdb), starts a server that listens only on the
loopback address 127.0.0.1, on a free port chosen for this run, with a password made up for this run (scram-sha-256;
the password is never printed), sends the SQL to psql, then stops the server and deletes the folder. Every run
therefore starts from an empty database and prints the same text. (The server opens no Unix socket: the temporary
folders of these runs are too deep for macOS's 103-byte limit on socket paths.)

The settings keep the output the same on every run: no autovacuum (statistics change only when the SQL runs ANALYZE
or VACUUM), statistics taken from every row instead of a random sample (default_statistics_target = 10000), no
parallel workers, no JIT compilation, and WAL buffers large enough never to fill up during a run. The cluster uses the
C locale, UTF-8 and UTC.

PostgreSQL 18.6 comes from the folder where mise installs it (mise install postgres@18.6). That folder is in the home
folder, which the isolated runs of learn:verify may not read, so the examples that use this file are recorded with its
network isolation off (LEARN_VERIFY_NETWORK=allow); the server accepts connections from this computer only. The
output is psql's own, except that the "psql:<stdin>:<line>:" in front of each message is left out and the run's port
number is printed as "<port>". Each call of psql() is a new psql process, so a new connection, on the same server.
"""

import atexit
import os
import pwd
import re
import secrets
import shutil
import socket
import subprocess
import sys

BIN = os.path.join(pwd.getpwuid(os.getuid()).pw_dir, ".local", "share", "mise", "installs", "postgres", "18.6", "bin")
WHERE = re.compile(r"^psql:<stdin>:\d+: ", re.MULTILINE)

_base = os.path.join(os.path.normpath(os.environ.get("TMPDIR", os.getcwd())), "pg")
_data = os.path.join(_base, "d")
_running = False
_port = ""
_env: dict[str, str] = {}


def _free_port() -> str:
    with socket.socket(socket.AF_INET, socket.SOCK_STREAM) as s:
        s.bind(("127.0.0.1", 0))
        return str(s.getsockname()[1])


def _quiet(*argv: str) -> None:
    subprocess.run(argv, env=_env, check=True, stdout=subprocess.DEVNULL, stderr=subprocess.DEVNULL)


def _start() -> None:
    global _running, _port, _env
    shutil.rmtree(_base, ignore_errors=True)
    os.makedirs(_base)
    password = secrets.token_urlsafe(18)
    pwfile = os.path.join(_base, "pw")
    with open(pwfile, "w") as f:
        f.write(password + "\n")
    _port = _free_port()
    _env = dict(os.environ, PGHOST="127.0.0.1", PGPORT=_port, PGUSER="postgres", PGDATABASE="postgres",
                PGPASSWORD=password, LC_ALL="C", LANG="C", TZ="UTC")
    _quiet(os.path.join(BIN, "initdb"), "-D", _data, "-U", "postgres", "-A", "scram-sha-256", f"--pwfile={pwfile}",
           "-E", "UTF8", "--locale=C", "--no-sync", "--no-instructions")
    os.remove(pwfile)
    options = (
        f"-c listen_addresses=127.0.0.1 -c port={_port} -c unix_socket_directories='' -c fsync=off -c timezone=UTC"
        " -c autovacuum=off -c default_statistics_target=10000 -c max_parallel_workers_per_gather=0 -c jit=off"
        " -c wal_buffers=64MB"
    )
    _quiet(os.path.join(BIN, "pg_ctl"), "start", "-w", "-s", "-D", _data, "-l", os.path.join(_base, "server.log"), "-o", options)
    _running = True
    atexit.register(_stop)


def _stop() -> None:
    global _running
    if _running:
        subprocess.run([os.path.join(BIN, "pg_ctl"), "stop", "-s", "-m", "fast", "-D", _data], env=_env, stdout=subprocess.DEVNULL, stderr=subprocess.DEVNULL)
        _running = False
    shutil.rmtree(_base, ignore_errors=True)


def _clean(text: str) -> str:
    return WHERE.sub("", text).replace(f"port {_port}", "port <port>")


def psql(sql: str) -> None:
    """Sends a block of SQL to psql and prints what it printed, errors included, in order."""
    if not _running:
        _start()
    run = subprocess.run(
        [os.path.join(BIN, "psql"), "-X", "-q", "-v", "ON_ERROR_STOP=0", "-P", "pager=off"],
        input=sql.strip() + "\n", env=_env, text=True, stdout=subprocess.PIPE, stderr=subprocess.STDOUT,
    )
    sys.stdout.write(_clean(run.stdout))
    sys.stdout.flush()

The identity column refused a key given by hand and named the way around it, OVERRIDING SYSTEM VALUE. The rolled-back insert took 4, the failed two-row insert took 5 and 6, and Lalremruati, inserted next, got 7: three numbers that no row will ever have. MySQL’s run follows:

"""MySQL 8.4.11: no RETURNING, LAST_INSERT_ID() after a two-row INSERT, the keys a failed INSERT uses up, what
INSERT IGNORE keeps, and a row of defaults."""

from mysql_server import mysql

print("-- 1. RETURNING is a syntax error in MySQL")
mysql("""
CREATE TABLE customers (
  customer_id INT         AUTO_INCREMENT PRIMARY KEY,
  name        VARCHAR(40) NOT NULL,
  city        VARCHAR(40) NOT NULL,
  plan        VARCHAR(10) NOT NULL DEFAULT 'basic'
);
INSERT INTO customers (name, city) VALUES ('Asha Kulkarni', 'Pune') RETURNING customer_id;
""")
print("-- 2. Two rows in one INSERT: LAST_INSERT_ID() is the key of the first one")
mysql("""
INSERT INTO customers (name, city) VALUES ('Asha Kulkarni', 'Pune'), ('Vikram Nair', 'Kochi');
SELECT LAST_INSERT_ID(), ROW_COUNT();
""")
print("-- 3. One NULL city: nothing is inserted, yet two keys are used up")
mysql("""
INSERT INTO customers (name, city) VALUES ('Meera Rathore', 'Jaipur'), ('Tenzin Dorjee', NULL);
SELECT count(*) FROM customers;
""")
print("-- 4. INSERT IGNORE keeps the broken row, with '' in place of NULL, and a warning")
mysql("""
INSERT IGNORE INTO customers (name, city) VALUES ('Meera Rathore', 'Jaipur'), ('Tenzin Dorjee', NULL);
SHOW WARNINGS;
SELECT customer_id, name, city, plan FROM customers ORDER BY customer_id;
""")
print("-- 5. A row of defaults: DEFAULT VALUES is a syntax error, () VALUES () works")
mysql("""
CREATE TABLE visits (
  visit_id INT         AUTO_INCREMENT PRIMARY KEY,
  page     VARCHAR(40) NOT NULL DEFAULT '/',
  seconds  INT         NOT NULL DEFAULT 0
);
INSERT INTO visits DEFAULT VALUES;
INSERT INTO visits () VALUES ();
SELECT * FROM visits;
""")

-- 1. RETURNING is a syntax error in MySQL
ERROR 1064 (42000) at line 7: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'RETURNING customer_id' at line 1
-- 2. Two rows in one INSERT: LAST_INSERT_ID() is the key of the first one
+------------------+-------------+
| LAST_INSERT_ID() | ROW_COUNT() |
+------------------+-------------+
|                1 |           2 |
+------------------+-------------+
-- 3. One NULL city: nothing is inserted, yet two keys are used up
ERROR 1048 (23000) at line 1: Column 'city' cannot be null
+----------+
| count(*) |
+----------+
|        2 |
+----------+
-- 4. INSERT IGNORE keeps the broken row, with '' in place of NULL, and a warning
+---------+------+------------------------------+
| Level   | Code | Message                      |
+---------+------+------------------------------+
| Warning | 1048 | Column 'city' cannot be null |
+---------+------+------------------------------+
+-------------+---------------+--------+-------+
| customer_id | name          | city   | plan  |
+-------------+---------------+--------+-------+
|           1 | Asha Kulkarni | Pune   | basic |
|           2 | Vikram Nair   | Kochi  | basic |
|           5 | Meera Rathore | Jaipur | basic |
|           6 | Tenzin Dorjee |        | basic |
+-------------+---------------+--------+-------+
-- 5. A row of defaults: DEFAULT VALUES is a syntax error, () VALUES () works
ERROR 1064 (42000) at line 6: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DEFAULT VALUES' at line 1
+----------+------+---------+
| visit_id | page | seconds |
+----------+------+---------+
|        1 | /    |       0 |
+----------+------+---------+

"""Runs SQL on a private, throwaway MySQL 8.4.11 server and prints what the mysql client printed.

Each run makes a new data folder in its own temporary folder, starts a server that listens only on the loopback
address 127.0.0.1, on a free port chosen for this run, sets a root password made up for this run (it is never
printed), creates the database "shop", sends the SQL to the mysql client, then shuts the server down and deletes the
folder. Every run starts from an empty database. (The server's Unix socket is named relative to its data folder and
used by no client: the temporary folders of these runs are too deep for macOS's limit on socket paths.)

Two settings keep the plans the same on every run: InnoDB recomputes statistics only when the SQL runs ANALYZE TABLE
(innodb_stats_auto_recalc=OFF), and ANALYZE TABLE reads every page instead of a random sample
(innodb_stats_persistent_sample_pages). The server's clock is UTC.

Every call of mysql() starts a new client, so a new connection, as root in the database "shop" or in none. The
client's warning about a password on its command line is left out of the output.

MySQL 8.4.11 comes from the folder where mise installs it (mise install mysql@8.4.11). That folder is in the home
folder, which the isolated runs of learn:verify may not read, so the examples that use this file are recorded with its
network isolation off (LEARN_VERIFY_NETWORK=allow); the server accepts connections from this computer only.
"""

import atexit
import os
import pwd
import secrets
import shutil
import socket
import subprocess
import sys
import time

HOME = os.path.join(pwd.getpwuid(os.getuid()).pw_dir, ".local", "share", "mise", "installs", "mysql", "8.4.11")
BIN = os.path.join(HOME, "bin")
WARNING = "mysql: [Warning] Using a password on the command line interface can be insecure.\n"

_base = os.path.join(os.environ.get("TMPDIR", os.getcwd()), "my")
_data = os.path.join(_base, "d")
_server = None
_port = ""
_password = secrets.token_urlsafe(18)


def _free_port() -> str:
    with socket.socket(socket.AF_INET, socket.SOCK_STREAM) as s:
        s.bind(("127.0.0.1", 0))
        return str(s.getsockname()[1])


def _client(*args: str, sql: str = "", password: str | None = None) -> subprocess.CompletedProcess:
    login = ["-uroot"] + ([f"--password={password}"] if password is not None else [])
    return subprocess.run(
        [os.path.join(BIN, "mysql"), "--no-defaults", "--protocol=TCP", "--host=127.0.0.1", f"--port={_port}", *login, *args],
        input=sql, text=True, stdout=subprocess.PIPE, stderr=subprocess.STDOUT,
    )


def _start() -> None:
    global _server, _port
    shutil.rmtree(_base, ignore_errors=True)
    os.makedirs(_base)
    _port = _free_port()
    init = os.path.join(_base, "init.sql")
    with open(init, "w") as f:
        f.write(f"CREATE USER 'root'@'127.0.0.1' IDENTIFIED BY '{_password}';\n"
                "GRANT ALL ON *.* TO 'root'@'127.0.0.1' WITH GRANT OPTION;\n"
                f"ALTER USER 'root'@'localhost' IDENTIFIED BY '{_password}';\n"
                "CREATE DATABASE shop;\n")
    server = [
        os.path.join(BIN, "mysqld"), "--no-defaults", f"--basedir={HOME}", f"--datadir={_data}",
        "--socket=m.sock", "--bind-address=127.0.0.1", f"--port={_port}", "--mysqlx=OFF",
        f"--pid-file={os.path.join(_base, 'pid')}", f"--log-error={os.path.join(_base, 'error.log')}", "--skip-log-bin",
        "--performance-schema=OFF", "--innodb-stats-auto-recalc=OFF", "--innodb-stats-persistent-sample-pages=100000",
        "--default-time-zone=+00:00", "--skip-name-resolve",
    ]
    subprocess.run(server[:4] + ["--initialize-insecure", f"--log-error={os.path.join(_base, 'init.log')}"], check=True, stdout=subprocess.DEVNULL, stderr=subprocess.DEVNULL)
    _server = subprocess.Popen(server + [f"--init-file={init}"], stdout=subprocess.DEVNULL, stderr=subprocess.DEVNULL)
    atexit.register(_stop)
    for _ in range(600):
        if _client("-e", "SELECT 1", password=_password).returncode == 0:
            os.remove(init)
            return
        time.sleep(0.1)
    raise RuntimeError("the MySQL server did not start")


def _stop() -> None:
    global _server
    if _server is not None:
        subprocess.run([os.path.join(BIN, "mysqladmin"), "--no-defaults", "--protocol=TCP", "--host=127.0.0.1", f"--port={_port}", "-uroot", f"--password={_password}", "shutdown"], stdout=subprocess.DEVNULL, stderr=subprocess.DEVNULL)
        try:
            _server.wait(timeout=60)
        except subprocess.TimeoutExpired:
            _server.kill()
        _server = None
    shutil.rmtree(_base, ignore_errors=True)


def mysql(sql: str, database: str | None = "shop") -> None:
    """Sends a block of SQL to the mysql client as root, in `database` (tables as boxes), and prints its output."""
    if _server is None:
        _start()
    run = _client("--table", "--force", "--comments", *([database] if database else []), sql=sql.strip() + "\n", password=_password)
    sys.stdout.write(run.stdout.replace(WARNING, ""))
    sys.stdout.flush()

MySQL answered RETURNING and DEFAULT VALUES with error 1064, a syntax error. After the two-row insert, LAST_INSERT_ID() returned 1, the key of the first row, and ROW_COUNT() 2. The failed insert used up 3 and 4, and INSERT IGNORE then kept Tenzin Dorjee with an empty city and warning 1048, where SQLite’s INSERT OR IGNORE would have skipped the row.

Practise: add products and read back their keys

The exercise adds three products with one statement and returns their new keys. Its sample tests run in your browser, on tables with a gap in their numbers and with an extra column that has a default:

-- Add the three new products in one INSERT statement, and let SQLite choose each
-- product_id. Return product_id, name and price_paise of the new rows.
SELECT product_id, name, price_paise FROM products WHERE product_id IS NULL;

# Sample tests: each one builds a products table, runs your statement on it and compares the rows it returns (in any
# order).
tests:
  - name: returns the three new products with the numbers SQLite chose
    setup: |
      CREATE TABLE products (product_id INTEGER PRIMARY KEY, name TEXT NOT NULL, category TEXT NOT NULL, price_paise INTEGER NOT NULL);
      INSERT INTO products (product_id, name, category, price_paise) VALUES
        (1, 'Basmati rice 1 kg', 'Grains', 14999),
        (2, 'Toor dal 500 g', 'Pulses', 8950),
        (3, 'Masala chai 250 g', 'Beverages', 12000),
        (4, 'Coconut oil 1 L', 'Oils', 21500),
        (5, 'Ragi flour 1 kg', 'Grains', 6800);
    expected:
      columns: [product_id, name, price_paise]
      rows:
        - [6, 'Jowar flour 1 kg', 7200]
        - [7, 'Moong dal 500 g', 9400]
        - [8, 'Filter coffee 200 g', 18500]
  - name: continues after the largest number, even when there is a gap
    setup: |
      CREATE TABLE products (product_id INTEGER PRIMARY KEY, name TEXT NOT NULL, category TEXT NOT NULL, price_paise INTEGER NOT NULL);
      INSERT INTO products (product_id, name, category, price_paise) VALUES
        (1, 'Basmati rice 1 kg', 'Grains', 14999),
        (2, 'Toor dal 500 g', 'Pulses', 8950),
        (40, 'Jaggery 1 kg', 'Sweeteners', 9900);
    expected:
      columns: [product_id, name, price_paise]
      rows:
        - [41, 'Jowar flour 1 kg', 7200]
        - [42, 'Moong dal 500 g', 9400]
        - [43, 'Filter coffee 200 g', 18500]
  - name: leaves a column it does not mention to its default
    setup: |
      CREATE TABLE products (product_id INTEGER PRIMARY KEY, name TEXT NOT NULL, in_stock INTEGER NOT NULL DEFAULT 1, category TEXT NOT NULL, price_paise INTEGER NOT NULL);
      INSERT INTO products (product_id, name, in_stock, category, price_paise) VALUES
        (1, 'Basmati rice 1 kg', 1, 'Grains', 14999),
        (2, 'Toor dal 500 g', 0, 'Pulses', 8950);
    expected:
      columns: [product_id, name, price_paise]
      rows:
        - [3, 'Jowar flour 1 kg', 7200]
        - [4, 'Moong dal 500 g', 9400]
        - [5, 'Filter coffee 200 g', 18500]

The questions below cover the seven ideas of this lesson, from the column list to the engines, and each round begins with the idea you have answered worst so far:

Interview questions

Fresher · What is the difference between leaving a column out of an INSERT and inserting NULL into it?

Leaving the column out of the column list lets the database fill it: with the column’s DEFAULT when it has one, otherwise with NULL. Writing NULL is giving a value, so the default is not used, and a NOT NULL column refuses the row with an error, default or not. In PostgreSQL and MySQL the keyword DEFAULT in a VALUES list asks for the default explicitly; SQLite has no such keyword, so there you leave the column out. The difference matters for programs that always send every column: an ORM or a form handler that turns empty fields into NULL never gets the defaults, so plan stays NULL instead of 'basic', or the insert fails. A default also applies only when a row is inserted; it never fills in NULLs that are already stored.

Fresher · How do you get the id of a row you have just inserted, in PostgreSQL, MySQL and SQLite?

In PostgreSQL, add RETURNING id to the INSERT: the statement returns the new rows, one per inserted row, with any columns you name. SQLite supports the same RETURNING clause from version 3.35, and also has last_insert_rowid() for the last row its connection inserted. MySQL has no RETURNING, so you call SELECT LAST_INSERT_ID() on the same connection right after the INSERT; the value belongs to that connection, so other sessions inserting at the same time cannot change it, and after a multi-row INSERT it is the key of the first row. Never use SELECT max(id): another connection can insert between your two statements, and you would read its id instead of yours.

Two longer questions of this module start from this lesson:

Key takeaways

• INSERT INTO t (a, b) VALUES (…), (…) adds one row per list of values, matched to the column list by position, so write the column list in every INSERT that lives in code.
• A multi-row INSERT succeeds or fails as a whole, and the ways to keep the good rows differ: SQLite’s INSERT OR IGNORE skips a broken row, while MySQL’s INSERT IGNORE keeps a NULL-breaking row with a default.
• A column left out of the list gets its DEFAULT, or NULL, while an explicit NULL is a value that replaces the default.
• INSERT … SELECT copies a query’s rows into the column list by position, and a constant in the SELECT list gives every copied row the same value.
• A new row’s key comes from the INSERT itself, with RETURNING in PostgreSQL and SQLite or LAST_INSERT_ID() in MySQL, which reports the first key of a multi-row insert, and never from max(id).
• Keys are unique, not consecutive: failed and rolled-back inserts leave gaps, and SQLite without AUTOINCREMENT can give a deleted largest key out again.