CODEWITHSUNDEEP
sqlpostgresql
Alfred Balle
Alfred Balle

Reputation: 1195

PostgreSQL more than one row returned by a subquery used as an expression

Running PostgreSQL 9.6. I'm trying to output rows consisting of a value and a list of names. This is my query:

SELECT name, (SELECT car_name FROM cars WHERE user = id)
FROM users WHERE user_id = 1 ORDER BY name;

But it fails with:

ERROR:  more than one row returned by a subquery used as an expression

It of course make sense, but I would like to have the nested query to be outputted as a list or json. I've tried with row_to_json, but that fails also.

Upvotes: 4

Views: 25715

Answers (2)

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

You can do the JOIN instead :

SELECT u.name, string_agg(c.car_name)
FROM users u LEFT OUTER JOIN
     cars c
     ON c.id = u.user
WHERE u.user_id = 1
GROUP BY u.name
ORDER BY u.name;

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269773

Use an aggregation function, such as string_agg() or json_agg():

SELECT name,
       (SELECT string_agg(car_name) FROM cars WHERE user = id)
FROM users
WHERE user_id = 1
ORDER BY name;

Upvotes: 5

Related Questions