(opens in a new tab)

INSERT Has More Expressions Than Target Columns

November 9, 2026 by Chat2DB Team

PostgreSQL checks the shape of an INSERT before it looks at a single value. If the number of values does not line up with the number of columns, the statement fails with one of these messages:

ERROR:  INSERT has more expressions than target columns
ERROR:  INSERT has more target columns than expressions
ERROR:  VALUES lists must all be the same length

All three have SQLSTATE 42601 (syntax error), and the caret in the LINE output points at the first value or column that does not fit. This article walks through each variant, the less obvious ways to trigger them (extra parentheses, SELECT *, a table that gained a column), and how to write inserts that keep working when the table changes. We ran every example on PostgreSQL 18.6, and the messages are identical on 14.24.

The Example Table

CREATE TABLE customers (
  id         int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  name       text NOT NULL,
  email      text,
  created_at timestamptz DEFAULT now()
);

The table has four columns. Two of them, id and created_at, have defaults, so most inserts only need to supply name and email.

More Expressions Than Target Columns

The most common form: the column list names two columns, but the VALUES row has three values.

INSERT INTO customers (name, email)
VALUES ('Ana', 'ana@example.com', now());

ERROR:  INSERT has more expressions than target columns
LINE 2: VALUES ('Ana', 'ana@example.com', now());
                                          ^

The caret marks now(), the first value with no column to go into. The fix is to either add the column to the list or remove the value:

INSERT INTO customers (name, email, created_at)
VALUES ('Ana', 'ana@example.com', now());

Without a column list, PostgreSQL uses all of the table's columns in their defined order. Five values for a four-column table produce the same error:

INSERT INTO customers VALUES (1, 'Ana', 'ana@example.com', now(), 'extra');

ERROR:  INSERT has more expressions than target columns

More Target Columns Than Expressions

The mirror image: the column list is longer than the row.

INSERT INTO customers (name, email, created_at)
VALUES ('Bea', 'bea@example.com');

ERROR:  INSERT has more target columns than expressions
LINE 1: INSERT INTO customers (name, email, created_at)
                                            ^

This time the caret points at the column list, at created_at, the first column with no value. Note the asymmetry with the previous case. When you omit the column list entirely, PostgreSQL does not complain about missing values. It fills the trailing columns with their defaults. When you write a column list, every listed column needs a value. If you want the default for a listed column, say so with the DEFAULT keyword:

INSERT INTO customers (name, email, created_at)
VALUES ('Bea', 'bea@example.com', DEFAULT)
RETURNING id, name;

The Extra Parentheses Trap

This version confuses people because the number of values looks right:

INSERT INTO customers (name, email)
VALUES (('Cy', 'cy@example.com'));

ERROR:  INSERT has more target columns than expressions
LINE 1: INSERT INTO customers (name, email)
                                     ^
HINT:  The insertion source is a row expression containing the same number of columns expected by the INSERT. Did you accidentally use extra parentheses?

The inner parentheses turn the two values into a single row constructor, so the VALUES row contains one expression of a composite type, not two text values. PostgreSQL notices that the row expression has exactly the right number of fields and says so in the hint. Remove one pair of parentheses:

INSERT INTO customers (name, email)
VALUES ('Cy', 'cy@example.com');

Extra parentheses usually come from string building. A helper that wraps each row in (...) combined with a template that already has VALUES (...) produces exactly this statement. Query builders that accept "a tuple" can also do it if you pass a list of tuples where they expected one tuple.

VALUES Lists Must All Be the Same Length

A multi-row VALUES clause must have the same number of values in every row:

INSERT INTO customers (name, email)
VALUES ('Dee', 'dee@example.com'), ('Eve');

ERROR:  VALUES lists must all be the same length
LINE 2: VALUES ('Dee', 'dee@example.com'), ('Eve');
                                            ^

The caret points at the first row whose length differs from the first row. This check happens before the comparison with the column list, so even a VALUES clause where one row matches the column list fails if another does not:

INSERT INTO customers (name) VALUES ('Fay'), ('Gus', NULL);

ERROR:  VALUES lists must all be the same length

If some rows genuinely have no value for a column, write NULL or DEFAULT in that position instead of leaving it out.

INSERT ... SELECT

The same rules apply when the source is a query. The select list is compared with the column list:

CREATE TABLE customers_archive (
  id          int,
  name        text,
  email       text,
  created_at  timestamptz,
  archived_at timestamptz DEFAULT now()
);
 
INSERT INTO customers (name, email)
SELECT name, email, created_at FROM customers_archive;

ERROR:  INSERT has more expressions than target columns
LINE 2: SELECT name, email, created_at FROM customers_archive;
                            ^

SELECT * hides the count, which makes it the most common cause in copy and archive jobs. Here the archive table has five columns and customers has four:

INSERT INTO customers SELECT * FROM customers_archive;

ERROR:  INSERT has more expressions than target columns

The opposite direction works, which surprises people:

INSERT INTO customers_archive SELECT * FROM customers;

INSERT 0 3

The three rows are copied. Four values per row go into the first four columns of the archive table, and archived_at gets its default. The statement succeeds today. It is still fragile: the values are matched to columns by position, not by name. If someone later adds a column to customers, or reorders the archive table, this statement either starts failing or, worse, silently writes values into the wrong columns when the types happen to be compatible. Always name both sides:

INSERT INTO customers_archive (id, name, email, created_at)
SELECT id, name, email, created_at FROM customers;

When a Column Is Added Later

A frequent production version of this error has nothing to do with the code that changed. A migration adds a column, and an old INSERT without a column list keeps running:

• If the new column was added at the end and has a default or allows NULL, the old insert still works, because missing trailing values take the default.
• If the old insert supplied every column and someone dropped a column, it now has one value too many and fails with "more expressions than target columns".
• If a job inserts SELECT * from one table into another and only one of them got the new column, it fails in one direction and silently shifts in the other.

The durable fix is the same in every case: list the target columns explicitly in every INSERT that lives in code, scripts or scheduled jobs. Column lists cost a few characters and make the statement independent of column order.

Finding Positional Inserts Before They Break

Positional inserts are easy to find before a migration exposes them. Search the code base, SQL files and scheduled jobs for INSERT INTO followed directly by a table name and VALUES or SELECT, with no opening parenthesis for a column list in between. Pay special attention to INSERT INTO ... SELECT *, which depends on the column order of two tables at once. On the database side, pg_stat_statements keeps normalized statement texts, so a query for INSERT INTO statements without a column list there shows what the applications actually send. Fixing those statements before you add or drop a column turns a production incident into a routine change.

A Syntax Error That Looks Similar

A missing comma inside the column list or VALUES row produces a different error, not a count mismatch:

INSERT INTO customers (name, email) VALUES ('Hal' 'hal@example.com');

ERROR:  syntax error at or near "'hal@example.com'"

A trailing comma also produces a syntax error:

INSERT INTO customers (name, email) VALUES ('Ivy', 'ivy@example.com',);

ERROR:  syntax error at or near ")"

If you see syntax error at or near instead of the count messages, look for a missing or extra comma first. The syntax error at or near guide covers the other common causes.

Patterns That Keep Inserts Correct

These habits prevent all of the errors above:

1. Always write a column list. INSERT INTO t (a, b, c) VALUES (...) is clear about which value goes where, and it keeps working when columns are added to the end of the table.
2. Use DEFAULT instead of omitting a listed column. If a column is in the list, give it a value or the DEFAULT keyword.
3. Name the columns of derived tables. When inserting from VALUES used as a subquery, give it column names so the select list is explicit:

INSERT INTO customers (name, email)
SELECT name, email
FROM (VALUES ('Jo', 'jo@example.com'), ('Kai', 'kai@example.com')) AS v(name, email)
RETURNING id;

4. Check the driver output. If your code builds multi-row inserts, log the generated SQL once and count the placeholders per row. Most count mismatches in application code come from a row builder that skips a field when its value is undefined.
5. Let your SQL client show the column list. In Chat2DB (opens in a new tab), you can generate an INSERT template from the table definition, so the column list and the placeholders always match, and the AI assistant can rewrite a positional INSERT ... SELECT * into one with named columns.

Summary

The common thread is that PostgreSQL matches values to columns by position. Make the positions explicit with a column list and these errors become rare, and the silent column shifts that are worse than any error disappear too.