PostgreSQL: output rows as list of comma-separated strings

Question

On PostgreSQL 11, I’d like to output the rows of a table as a comma-separated list of strings:

id,info,name,message

I tried the following query:

SELECT array_to_string(array_agg(t), ',', '')                                                                                                                          
FROM (                                                                      
    SELECT id, info, name, message
    FROM myschema.mytable
) t;

But this outputs:

(1,foo,bar,baz),(2,qux,zap,xen)

In constrast, the desired result should look like:

1,foo,bar,baz
2,qux,zap,xen

What would be the correct query?

Gordon Linoff · Accepted Answer · 2020-03-24 12:22:49Z

1

You seem to want:

SELECT id, CONCAT_WS(',', info, name, message)
FROM myschema.mytable;

No aggregation is necessary. The values you want are all in one row.

You can, of course, include id in the string instead of as a separate column:

SELECT CONCAT_WS(',', id, info, name, message)
FROM myschema.mytable;

answered Mar 24, 2020 at 12:22

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

laserbrain · Accepted Answer · 2020-03-24 04:58:55Z

0

I could solve it with the following function:

CREATE OR REPLACE FUNCTION myschema.get_foo()
RETURNS SETOF text AS $$
BEGIN
    RETURN QUERY
        SELECT COALESCE(array_to_string(array[id::text, info::text, name::text, message::text], ','), ''::text)
        FROM myschema.mytable;
END;
$$ LANGUAGE plpgsql;

And then run:

SELECT * FROM myschema.get_foo();

answered Mar 24, 2020 at 4:58

laserbrain

1,0553 gold badges11 silver badges23 bronze badges

Collectives™ on Stack Overflow

PostgreSQL: output rows as list of comma-separated strings

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related