52

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?

Community
  • 1
  • 1
joe
  • 8,383
  • 13
  • 61
  • 109

1 Answers1

111

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

Abhishek Ginani
  • 4,511
  • 4
  • 23
  • 35