`SELECT a.*, array_agg(b)` requires `GROUP BY` for all columns in `a`

user_3579379 0
Viewed 341

Question: How can I specify I want an array of child related to the parent by uniq_hash (and all the other columns) without actually specifying every single column in the GROUP BY?

The database is PostgreSQL.

I have these tables: parents and children

parents

| id | col1 | col2 | ... | col250 | uniq_hash |
|----|------|------|-----|--------|-----------|
| 1  |      |      |     |        | <hash>    |

children

| id | bcol1 | bcol2 | ... | bcol50 | uniq_hash |
|----|-------|-------|-----|--------|-----------|
| 1  |       |       |     |        | <hash>    |

       +---------+
   +---+  Parent +---+
   |   +----+----+   |
   |        |        |
+--v--+  +--v--+  +--v--+
|Child|  |Child|  |Child|
+-----+  +-----+  +-----+

I'm trying to add an array of child to a SELECT * parent FROM parents WHERE ...; query.

The resulting query would look something like:

SELECT parents.*, array_agg(children) as children FROM parents LEFT JOIN children ON parents.uniq_hash = children.uniq_hash WHERE ...;

The issue is that parents is ~250 columns wide, and to do that it appears that I need to list every single column in parents as a GROUP BY. This is... suboptimal.

I have tried this, and it executes - but it just returns the two columns (ie: it's missing headers on the parent data column):

SELECT parents, array_agg(children) as children FROM parents LEFT JOIN children ON parents.uniq_hash = children.uniq_hash WHERE ... GROUP BY parents;

postgresql sql
1 Answers

In fact, you don't. You can just use:

GROUP BY parents.id

And if id is declared to be unique or primary key, then Postgres will accept the syntax.

If the id is not declared in this way, you can use a correlated subquery:

SELECT p.*,
       (SELECT array_agg(c.children)
        FROM children c
        WHERE p.uniq_hash = c.uniq_hash
       ) as children
FROM parents p;
user_11440350
Related