Maaz Zindani

Loading the index…

↑↓ move↵ openesc close4072 pages
Databases and Redis

ERROR: subquery must return only one column

ErrorERROR: subquery must return only one column

Short 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.

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

Fix it fast

  1. 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;
  2. 2Match what you see, then jump to its fix

    1. 1WHERE x IN (SELECT a, b FROM ...) or SELECT * inside the parenthesesIN or ANY compares one value with a subquery that returns several columnsFix
    2. 2SELECT name, (SELECT a, b FROM ...) FROM ...A scalar subquery in the SELECT list returns more than one columnFix

What does the error look like?

  • ERROR: subquery must return only one column
  • error : 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 orders

2Fix

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

Did this fix it?

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 customers

Confirmed by postgresql.org

Did this fix it?

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

Next step

Want this handled for good?

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