Fix “Column Count Doesn't Match Value Count” in SQL

The symptom

An INSERT fails before it adds a row. MySQL says the column count doesn't match the value count at row 1, PostgreSQL says the INSERT has more expressions than target columns, and DuckDB says the table has 4 columns but 3 values were supplied.

Without a column list, an INSERT fills every column, in the order the table defines them, so it needs one value for each. The table below has four columns, and the INSERT leaves out status, expecting its default to fill it:

The fix: name the columns you fill

create or replace table restock_list (
  product_id   integer,
  product_name varchar,
  units        integer,
  status       varchar default 'draft'
);
insert into restock_list values (4, 'Pothos', 16);

create or replace table restock_list (
  product_id   integer,
  product_name varchar,
  units        integer,
  status       varchar default 'draft'
);
insert into restock_list (product_id, product_name, units)
values (4, 'Pothos', 16);
select * from restock_list;

With a column list, the values only have to match the list. status is left out, so it gets its default, 'draft'. The list also protects the INSERT from later changes: when someone adds a column to the table, an INSERT without a list starts failing with this error, and one with a list keeps working.

To fill every column but let one take its default, keep all the values and write default in that position:

create or replace table restock_list (
  product_id   integer,
  product_name varchar,
  units        integer,
  status       varchar default 'draft'
);
insert into restock_list values (4, 'Pothos', 16, default);
insert into restock_list values (7, 'Tomato Seeds', 75, default);
select * from restock_list order by product_id;

A list and values that disagree

With a column list, the count has to match the list instead. DuckDB words that case differently:

insert into restock_list (product_id, product_name)
values (11, 'Hand Cultivator', 17);

It fails with Column name/value mismatch for insert on restock_list: expected 2 columns but 3 values were supplied. Either add units to the list or drop the 17.

INSERT from a SELECT

The rule is the same when the rows come from a query: the SELECT must return one column per target column, in the same order. Column names don't matter, only positions:

insert into restock_list (product_id, product_name, units)
select product_id,
       product_name,
       reorder_level * 2 - quantity_on_hand
from products
where quantity_on_hand <= reorder_level
  and not discontinued
  and product_id not in (4, 7);
select * from restock_list order by product_id;

DuckDB can match by name instead: insert into restock_list by name select ... fills each column from the SELECT column with the same name and leaves the rest to their defaults. Databricks has the same option; PostgreSQL, MySQL, and SQL Server don't.

How other engines word it

Related errors

• Fix “Different Number of Columns” in a SQL UNION

  Each SELECT in a UNION has to return the same number of columns, in the same order.

• Fix “NOT NULL Constraint Failed” in SQL

  A required column got no value. Supply one, give the column a default, or let it be null.

• Fix “Cannot Insert Into a Generated Column” in SQL

  A generated column computes itself. Leave it out of the column list entirely.

Learn more

• SQL error messages explained
• Lesson 8.2 · Adding rows with INSERT
• Lesson 8.6 · INSERT from a SELECT
• Lesson 8.8 · Creating a table
• Reference · Upserts