I have relational database and would like to use string_agg()
since it seems fit to my need.
I want :
product_id | quiz_id
-----------+----------
1 | 1,6
2 | 2,7
3 | 3,8
4 | 4
Here is my database.
select quiz_id , product_id, lastmodified from dugong.quiz;
quiz_id | product_id | lastmodified
---------+------------+-------------------------------
1 | 1 | 2015-11-11 14:46:55.619162+07
2 | 2 | 2015-11-11 14:46:55.619162+07
3 | 3 | 2015-11-11 14:46:55.619162+07
4 | 4 | 2015-11-11 14:46:55.619162+07
5 | 5 | 2015-11-11 14:46:55.619162+07
6 | 1 | 2015-11-11 14:46:55.619162+07
7 | 2 | 2015-11-11 14:46:55.619162+07
8 | 3 | 2015-11-11 14:46:55.619162+07
My attempt :
Refer to document.
How to concatenate strings of a string field in a PostgreSQL 'group by' query?
http://www.postgresql.org/docs/current/static/sql-expressions.html#SYNTAX-AGGREGATES
select product_id , string_agg(quiz_id, ',' order by lastmodified) from dugong.quiz;
ERROR: function string_agg(integer, unknown) does not exist
LINE 1: select product_id , string_agg(quiz_id, ',' order by lastmod...
^
HINT: No function matches the given name and argument types. You might need to add explicit type casts.
Postgres version :
PostgresApp 9.4.4.1
Update :
@code-monk
It still error.
select product_id , string_agg(quiz_id::int, ',' order by lastmodified) from dugong.quiz;
ERROR: function string_agg(integer, unknown) does not exist
LINE 1: select product_id , string_agg(quiz_id::int, ',' order by la...
^
HINT: No function matches the given name and argument types. You might need to add explicit type casts.
Question :
What is wrong with my query?
Try this:
select product_id , string_agg(quiz_id::character varying, ',' order by lastmodified)
from quiz group by product_id;
String_agg
function works with String values only ,You are getting the error because quiz_id
is integer.
I have converted it to character varying
and added group by for grouping the data product ID wise.
SQL Fiddle Example:http://sqlfiddle.com/#!15/9dafe/1
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With