More than one row returned by a subquery when using STRING_AGG function

Question

I get an error when trying to execute SELECT query in PostgreSQL 11

select (
    SELECT STRING_AGG(u.first_name::text, ', ') 
    FROM game_authors AS gat 
    LEFT JOIN users AS u ON u.id = gat.user_id 
    WHERE gat.game_id = g.id AND gat.lang = 'uk' 
    GROUP BY gat.id ORDER BY gat.id ASC
) AS authors_string 
from "games" as "g" 
where "g"."status" != 10 
order by "g"."id" desc limit 10 offset 0

And authors_string should be fetched as a string value. It throwns an error

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

I guess it happens because of GROUP BY in subquery and can be handled with row_to_json function, but have no clue where I should to put it in the expression. ORDER BY doesn't work GROUP BY expression because aggregated function existed in SELECT.

SQL version

PostgreSQL 11.8 (Ubuntu 11.8-1.pgdg18.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 7.5.0-3ubuntu1~18.04) 7.5.0, 64-bit

GMB · Accepted Answer · 2020-10-20 15:31:08Z

2

I think you just want to remove the group by clause in the subquery:

select (
    select string_agg(u.first_name::text, ', ' order by gat.id asc) 
    from game_authors as gat 
    left join users as u on u.id = gat.user_id 
    where gat.game_id = g.id and gat.lang = 'uk' 
) as authors_string 
from games as g 
where g.status <> 10 
order by g.id desc 
limit 10

answered Oct 20, 2020 at 15:31

GMB

224k25 gold badges103 silver badges151 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

user330315 · Accepted Answer · 2020-10-21 06:55:15Z

0

Rather than a scalar sub-select, use a derived table. More often than not, this is more efficient:

select a.authors_string 
from games as g 
  join (
    SELECT gat.game_id, STRING_AGG(u.first_name::text, ', ' order by gat.id)  as authors
    FROM game_authors AS gat 
      LEFT JOIN users AS u ON u.id = gat.user_id 
    WHERE AND gat.lang = 'uk' 
    GROUP BY gat.id 
  ) a ON a.game_id = g.id
where g.status <> 10 
order by g.id desc 
limit 10 offset 0

The left join seems useless though

answered Oct 21, 2020 at 6:55

user330315

Collectives™ on Stack Overflow

More than one row returned by a subquery when using STRING_AGG function

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related