Skip to content

Instantly share code, notes, and snippets.

@kwatch
Last active August 29, 2015 14:03
Show Gist options
  • Save kwatch/dc208ba6f8809ae10fa3 to your computer and use it in GitHub Desktop.
Save kwatch/dc208ba6f8809ae10fa3 to your computer and use it in GitHub Desktop.
how to aggregate row data or complex type data?
--
-- data
--
psql=> select * from users;
id | name
----+-------
1 | Bob
2 | Alice
3 | John
(3 rows)
--
-- result: array of text
--
psql=> select array_agg(row(id, name)) from users;
array_agg
------------------------------------
{"(1,Bob)","(2,Alice)","(3,John)"}
(1 row)
--
-- what I want: array of tuple (integer, text)
--
psql=> select array_agg(row(id, name)) from users;
array_agg
------------------------------------
{(1,'Bob'),(2,'Alice'),(3,'John')}
(1 row)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment