Maaz Zindani

Loading the index…

↑↓ move↵ openesc close4072 pages
Databases and Redis

INSERT has more target columns than expressions, but the counts look equal

ErrorERROR: INSERT has more target columns than expressions

Short answer

PostgreSQL matches the values of an INSERT to the column list by position and counts both after expanding everything. The error means the column list is longer than the values. Count each row of a multi-row VALUES, remove extra parentheses around a SELECT list, and check that a * did not expand to more or fewer columns than you think.

  • Applies to PostgreSQL 16, PostgreSQL 17, PostgreSQL 18
  • Updated
  • By Maaz Zindani, Senior Software Engineer

Fix it fast

  1. 1Find out which cause you have

    Count the names in the column list, then count the values in each row. Print the columns the table really has, in case a SELECT * or INSERT without a column list is involved.

    SELECT ordinal_position, column_name
    FROM information_schema.columns
    WHERE table_name = 'orders'
    ORDER BY ordinal_position;
  2. 2Match what you see, then jump to its fix

    1. 1The error appears only for some inserts, usually ones built by code or pasted from a spreadsheetOne row of a multi-row VALUES is shorter than the column listFix
    2. 2The error comes with the hint about a row expression and extra parenthesesExtra parentheses turn the SELECT list into one row valueFix
    3. 3A statement that worked before fails after a column was added, or the source query uses *The column list and the query disagree after a table changeFix

What does the error look like?

  • ERROR: INSERT has more target columns than expressions
  • ERROR: INSERT has more expressions than target columns
  • HINT: The insertion source is a row expression containing the same number of columns expected by the INSERT. Did you accidentally use extra parentheses?

Cause 1 of 3

One row of a multi-row VALUES is shorter than the column list

1Check

The parser checks every row of a VALUES list on its own, so a single row with a missing value is enough. Look for a row with a dropped comma, a missing placeholder or a trailing column you removed from one place only.

-- 3 columns, but the second row has 2 values
INSERT INTO orders (id, customer, total) VALUES
  (1, 'a', 10.00),
  (2, 'b');

2Fix

Give every row a value for every listed column. Use the DEFAULT keyword where the column should take its default, or remove the column from the list.

INSERT INTO orders (id, customer, total) VALUES
  (1, 'a', 10.00),
  (2, 'b', DEFAULT);

3Verify

The statement runs and each row is stored.

SELECT * FROM orders ORDER BY id;

Confirmed by postgresql.org

Did this fix it?

Cause 2 of 3

Extra parentheses turn the SELECT list into one row value

1Check

In INSERT ... SELECT, writing the select list inside parentheses makes it a single composite value. PostgreSQL then sees one expression for several columns, and adds the hint when the composite has the right number of fields.

-- one expression (a row), three target columns
INSERT INTO orders (id, customer, total)
SELECT (id, customer, total) FROM staging_orders;

2Fix

Remove the parentheses around the select list.

INSERT INTO orders (id, customer, total)
SELECT id, customer, total FROM staging_orders;

3Verify

The insert reports the number of rows copied.

SELECT count(*) FROM orders;

Confirmed by raw.githubusercontent.com

Did this fix it?

Cause 3 of 3

The column list and the query disagree after a table change

1Check

Values are paired with columns left to right. With an explicit column list the query must produce exactly that many columns. Without a list, the first N columns of the table are used when only N values are supplied, so adding a column to the table changes what the statement means.

SELECT count(*) FROM information_schema.columns WHERE table_name = 'orders';
SELECT count(*) FROM information_schema.columns WHERE table_name = 'staging_orders';

2Fix

Name the columns on both sides instead of relying on * or on column order.

INSERT INTO orders (id, customer, total)
SELECT id, customer, total FROM staging_orders;

3Verify

Run the statement again after any change to either table.

SELECT * FROM orders ORDER BY id DESC LIMIT 5;

Confirmed by postgresql.org

Did this fix it?

How do I stop it coming back?

  • Always write the column list in INSERT statements that live in code or migrations.
  • Generate placeholders from the same array as the column names so the two cannot drift.
  • Use DEFAULT in VALUES for columns that should take their default.

Sources

How was this page made?

I drafted this page with AI assistance from the official documents listed under Sources. Every cause above names the document that confirms it. I have not reproduced this fix on a machine of my own yet, so read it as documented guidance, not a test report. Found a mistake? Tell me.

Which stack is behind this page?

Related fixes

Next step

Want this handled for good?

I look after servers, apps and releases end to end. Tell me what keeps breaking.