insert-value-count¶
Default severity: error
What it does¶
Reports an INSERT whose VALUES rows, or query, have a different number of
values than the columns it writes. Without a column list it writes the table's
columns in order: MySQL and SQLite want a value for each, and PostgreSQL at most
one for each.
[tool.sqlty]
schema = "schema.sql"
schema.sql:
CREATE TABLE users (
id SERIAL PRIMARY KEY,
email TEXT NOT NULL,
name TEXT
);
# error: [insert-value-count] "INSERT into `users` gives 1 value for 2 columns"
q = "--sql\nINSERT INTO users (email, name) VALUES (%s)"
# error: [insert-value-count] "INSERT into `users` gives 2 values for 1 column"
r = "--sql\nINSERT INTO users (email) SELECT email, name FROM users"
# error: [insert-value-count] "INSERT into `users` gives 4 values for 3 columns"
s = "--sql\nINSERT INTO users VALUES (DEFAULT, %s, %s, %s)"
Open in playgroundWhy is this bad?¶
The database rejects the statement. PostgreSQL reports INSERT has more target
columns than expressions, MySQL Column count doesn't match value count and
SQLite 2 values for 1 columns.
How to fix¶
Give one value for each column the INSERT names, or name the columns the
values are for.
Configuration¶
An error by default: the database rejects such SQL. Lower or turn it off:
[tool.sqlty.rules]
insert-value-count = "warn"
Needs a schema and is judged in the
postgres, mysql and sqlite dialects. A query source whose columns sqlty
can't count, such as SELECT * over a table it doesn't know, is not checked.