INSERT has more target columns than expressions, but the counts look equal
ERROR: INSERT has more target columns than expressionsShort 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.
Fix it fast
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;2Match what you see, then jump to its fix
- 1
The 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
The error comes with the hint about a row expression and extra parenthesesExtra parentheses turn the SELECT list into one row valueFix - 3
A 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
- 1
What does the error look like?
ERROR: INSERT has more target columns than expressionsERROR: INSERT has more expressions than target columnsHINT: 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
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
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
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
Databases
DBeaver cannot run pg_restore: the utility is not found in the client home
Utility 'pg_restore' not found in client home '/usr/lib' (/usr/lib)
DBeaver runs pg_restore from a local PostgreSQL client folder; it does not bundle one.
3 causes, each with a fixDatabases
DBeaver cannot download the PostgreSQL driver from Maven
The PostgreSQL driver files are missing and the download does not complete
DBeaver downloads the PostgreSQL JDBC driver from a Maven repository the first time you connect.
3 causes, each with a fixDatabases
python manage.py migrate finishes but the tables for my app are missing
relation "myapp_book" does not exist
migrate only applies migration files that exist.
3 causes, each with a fixDatabases
Django save fails on PostgreSQL: null value in a column violates a not-null constraint
django.db.utils.IntegrityError: null value in column "price" of relation "shop_product" violates not-null constraint
A model field is NOT NULL in the database unless it has null=True.
3 causes, each with a fixDatabases
Django migration on PostgreSQL stops with 'pending trigger events'
django.db.utils.OperationalError: cannot ALTER TABLE "mytable" because it has pending trigger events
PostgreSQL runs a Django migration in one transaction.
2 causes, each with a fixDatabases
Django cannot connect to PostgreSQL: connection refused on port 5432, locally or in Docker
Is the server running on that host and accepting TCP/IP connections?
Connection refused means nothing accepted the connection at the host and port Django used.
4 causes, each with a fix