fetch values into a comma separated string in postgresql

Question

This should be simple one. A query like

SELECT code_id FROM code WHERE status = 1

gives normally a result like

code_id
10
11
12

But my goal is to fetch into a string like

10,11,12

In order to use it in an other query

SELECT x FROM table WHERE status in (10,12,13)

Preferable in the same query. Is this possible using "standard" Postgresql WITHOUT adding extra extension?

Everything so far is using extension that not are available as standard.

Thanks in advance.

score 4 · Answer 1 · answered Nov 04 '15 at 14:48

4

You can try this way to get result as comma-separated

But my goal is to fetch into a string like

SELECT string_agg(code_id, ',') FROM code WHERE status = 1

answered Nov 04 '15 at 14:48

Always Sunny

this function does not exist in 9.1 as standard AFAIK. I get an error. – sibert Nov 04 '15 at 14:57
@sibert: this has been asked before - many times. One of them is: http://stackoverflow.com/q/2560946/330315 – a_horse_with_no_name Nov 04 '15 at 15:50
Yes, I know. But I could not find an answer using standard extensions. But **SELECT array_to_string(array(SELECT a FROM b),', ')** did work. Thank you! – sibert Nov 04 '15 at 16:07

score 4 · Accepted Answer · answered Nov 04 '15 at 14:48

4

Whatever tool you are using just shows you the data like that for convenience. But you can also use the resultset in a subquery, like this

SELECT x FROM table WHERE status in (
    SELECT code_id FROM code WHERE status = 1
)

answered Nov 04 '15 at 14:48

fvu

2 Answers2