Create an account

Very important

  • To access the important data of the forums, you must be active in each forum and especially in the leaks and database leaks section, send data and after sending the data and activity, data and important content will be opened and visible for you.
  • You will only see chat messages from people who are at or below your level.
  • More than 500,000 database leaks and millions of account leaks are waiting for you, so access and view with more activity.
  • Many important data are inactive and inaccessible for you, so open them with activity. (This will be done automatically)


Thread Rating:
  • 579 Vote(s) - 3.47 Average
  • 1
  • 2
  • 3
  • 4
  • 5
string_agg No function matches the given name

#1
I have relational database and would like to use `string_agg()` since it seems fit to my need.<br><br>
**I want :**<br>

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 :** <br>
Refer to document.

[To see links please register here]

[To see links please register here]


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.
<br>
**Postgres version :** <br>
PostgresApp 9.4.4.1

**Update :** <br>
@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 :**<br>
What is wrong with my query?
Reply

#2
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
Reply



Forum Jump:


Users browsing this thread:
1 Guest(s)

©0Day  2016 - 2023 | All Rights Reserved.  Made with    for the community. Connected through