How to convert several duplicate rows into an array in SQL (Postgres)?

Question

I have the following table One:

 id │ value 
────┼───────
  1 │ a
  2 │ b

And Two:

  id │ value 
─────┼───────
  10 │ a
  20 │ a
  30 │ b
  40 │ a
  50 │ b

One.value has a unique constraint but not Two.value (one-to-many relationship).

Which SQL (Postgres) query will retrieve as array the ids of Two whose value match One.value? The result I am looking for is:

          id │ value 
─────────────┼───────
  {10,20,40} │ a
     {30,50} │ b

vyegorov · Accepted Answer · 2014-10-20 17:13:46Z

5

Check on SQL Fiddle

SELECT array_agg(id) AS id, "value"
  FROM Two
 GROUP BY "value";

Using value as identifier (column name here) is a bad practice, as it is a reserved keyword.

answered Oct 20, 2014 at 17:13

vyegorov

23k7 gold badges61 silver badges76 bronze badges

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

1 Comment

Thank you for the extra tip about "value"