Skip to content

insert-missing-column

Default severity: error

What it does

Reports an INSERT that leaves out a schema column declared NOT NULL without a default. Without a column list, the values fill the table's columns in order: PostgreSQL gives the columns past them their defaults, and so do DEFAULT VALUES and MySQL's VALUES (). A DEFAULT, serial, identity, generated or auto-increment column, and SQLite's INTEGER PRIMARY KEY, get a value anyway. So does every column of a PostgreSQL or MySQL table with a BEFORE INSERT ... FOR EACH ROW trigger, which may set it.

[tool.sqlty]
schema = "schema.sql"

[tool.sqlty.rules]
insert-missing-column = "warn"

schema.sql:

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email TEXT NOT NULL,
    name TEXT NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    bio TEXT
);
# error: [insert-missing-column] "INSERT into `users` leaves out `name`, which is NOT NULL and has no default"
q = "--sql\nINSERT INTO users (email) VALUES (%s)"
# error: [insert-missing-column] "INSERT into `users` leaves out `name`, which is NOT NULL and has no default"
r = "--sql\nINSERT INTO users VALUES (DEFAULT, %s)"
Open in playground

Why is this bad?

The database rejects the row: the column has no value and may not be NULL. A migration that adds such a column breaks every INSERT written before it, which sqlty breaking reports.

How to fix

Set the column in the INSERT, or give it a DEFAULT in the schema.

Configuration

An error by default: the database rejects such SQL. Lower or turn it off:

[tool.sqlty.rules]
insert-missing-column = "warn"

Needs a schema, and is judged in the postgres, mysql and sqlite dialects. An INSERT without a column list from a query whose columns sqlty can't count is not checked.