Why This Error Happens
You run a query that worked perfectly yesterday, but today it crashes. PostgreSQL is telling you it expected a single value (a scalar), but your subquery handed it a list of two or more rows. This usually happens when a 1:1 relationship in your data unexpectedly turns into a 1:N (one-to-many) relationship.
Identifying the Root Cause
In SQL, a "scalar subquery" must return exactly one column and one row. If it returns zero rows, PostgreSQL treats the result as NULL. However, if it returns two or more rows, the engine doesn't know which value to use and stops execution. You will typically see this in three places:
- Inside a
SELECTlist to fetch a related value. - On the right side of a comparison operator like
=,<, or>. - In an
UPDATE ... SETclause when assigning a new value to a column.
Step-by-Step Fixes
1. Pinpoint the Problematic Data
Before changing code, find the rows causing the conflict. For example, if you are fetching emails based on a profile ID, check for duplicates. Use this query to find IDs associated with multiple records:
SELECT profile_id, COUNT(*)
FROM users
GROUP BY profile_id
HAVING COUNT(*) > 1;
If this returns results, your data integrity has slipped. You might have two users sharing the same profile_id, which breaks your logic.
2. Use LIMIT 1 for Quick Fixes
If you only care about the most recent record, add LIMIT 1. This is a common band-aid for reports where the exact match matters less than the query successfully finishing. Always pair this with ORDER BY to ensure your results are consistent.
SELECT
id,
(SELECT email FROM users
WHERE profile_id = profiles.id
ORDER BY created_at DESC LIMIT 1) as user_email
FROM profiles;
3. Swap '=' for 'IN' or 'ANY'
When your subquery is in a WHERE clause, the fix is often as simple as changing the operator. Using = forces PostgreSQL to look for one value. Switching to IN tells the database you are happy to match against a list of values.
The Breaking Query:
SELECT * FROM orders
WHERE user_id = (SELECT id FROM users WHERE status = 'active');
The Fixed Query:
SELECT * FROM orders
WHERE user_id IN (SELECT id FROM users WHERE status = 'active');
4. Aggregate the Results
Sometimes you need all the data, just not in multiple rows. If a user has three email addresses, you can use string_agg to combine them into a single, comma-separated string. This satisfies the "one row" requirement while preserving your data.
SELECT
id,
(SELECT string_agg(email, ', ') FROM users WHERE profile_id = profiles.id) as all_emails
FROM profiles;
5. Refactor to a JOIN (Best for Performance)
Subqueries in a SELECT list are often slow because they run once for every row in your result set. If you have 100,000 profiles, PostgreSQL might execute 100,000 subqueries. A LEFT JOIN is significantly more efficient and handles multiple matches by creating additional rows instead of crashing.
SELECT
p.id,
u.email
FROM profiles p
LEFT JOIN users u ON u.profile_id = p.id;
Verification Steps
After applying a fix, verify that your subquery logic no longer produces duplicates. Run your check query again:
SELECT profile_id FROM users GROUP BY profile_id HAVING COUNT(*) > 1;
If it returns zero rows, your 1:1 relationship is restored. If you used the JOIN or LIMIT approach, run the main query and ensure the row count matches your expectations. A JOIN might increase the total row count of your result set if duplicates still exist.
Pro Tips
- Prefer EXISTS: If you only need to check if a record exists, use
WHERE EXISTS (SELECT 1 FROM ...). It is faster than a scalar subquery and never throws the "more than one row" error. - Lateral Joins: For complex logic where you need the "top 1" related record but want better performance than a subquery, use
LEFT JOIN LATERAL. It allows you to run a subquery for each row while accessing columns from the parent table. - Database Constraints: Prevention is better than a late-night bug fix. If a column should only ever have one match, add a
UNIQUEconstraint. This forces the error to happen during data entry rather than during a critical read operation.