ERROR: subquery must return only one column
ERROR: subquery must return only one columnShort answer
Some places accept only a one-column subquery: IN, ANY and SOME, and a scalar subquery in the SELECT list or in an expression. If your subquery selects two or more columns there, PostgreSQL stops. Select one column, compare a row of columns, or move the subquery into FROM as a join.
Fix it fast
1Find out which cause you have
Find the subquery in parentheses that is used inside IN, ANY, =, or the SELECT list, and count the columns it selects. Run it alone to see them.
-- run the inner query on its own SELECT id, total FROM orders WHERE orders.customer_id = 1 LIMIT 5;2Match what you see, then jump to its fix
What does the error look like?
ERROR: subquery must return only one columnerror : subquery must return only one column
Cause 1 of 2
IN or ANY compares one value with a subquery that returns several columns
1Check
The right side of expression IN (subquery) and expression operator ANY (subquery) must return exactly one column. SELECT * in the subquery returns every column of the table.
\d orders2Fix
Select the single column you compare with. If you really compare two values, put a row of two expressions on the left, which needs a subquery with exactly that many columns.
-- one column
SELECT * FROM customers WHERE id IN (SELECT customer_id FROM orders);
-- or a row on the left
SELECT * FROM stock WHERE (sku, warehouse) IN (SELECT sku, warehouse FROM backorders);3Verify
The query runs and returns rows.
SELECT count(*) FROM customers WHERE id IN (SELECT customer_id FROM orders);Confirmed by postgresql.org
Cause 2 of 2
A scalar subquery in the SELECT list returns more than one column
1Check
A scalar subquery is a query in parentheses that returns exactly one row with one column. A second column is an error, and so are two rows. With no rows the value is null.
SELECT name, (SELECT max(total) FROM orders WHERE orders.customer_id = customers.id) FROM customers LIMIT 3;2Fix
Use one scalar subquery per value, or join a derived table once and read all its columns. A LATERAL subquery in FROM may refer to earlier FROM items, and LEFT JOIN LATERAL ... ON true keeps rows with no match.
SELECT c.name, o.id, o.total
FROM customers c
LEFT JOIN LATERAL (
SELECT id, total FROM orders WHERE orders.customer_id = c.id
ORDER BY total DESC LIMIT 1
) o ON true;3Verify
Every customer appears once, with the order columns null when there is none.
-- run the query above and compare the row count with SELECT count(*) FROM customersConfirmed by postgresql.org
How do I stop it coming back?
- Never write SELECT * inside IN, ANY or a scalar subquery; name the one column.
- When you need several values from another table, join it rather than adding a subquery per column.
- Run the inner query alone and look at its column count before you wrap it.
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
A subquery used as a single value returns several rows
ERROR: more than one row returned by a subquery used as an expression
PostgreSQL only accepts a subquery as a single value when it returns one row and one column.
3 causes, each with a fixDatabases
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 fix