PostgreSQL – How to Display Rows as CSV

postgresql

The default output format for a query is something like this:

ID | field1
-----------
1  | val1 
2  | val2

Is it possible in SQL to change it to a CSV format like this?

ID , field1
1  , val1 
2  , val2

Currently, I am converting fields to string values and then concatenating them using the || operator, e.g.

SELECT ID::text || ', ' || field1 ...  
FROM ...

But this is really cumbersome to write, and not very readable either.

Is there a more succinct way of doing this without converting to strings?

This isn't a duplicate of Export remote Postgres table to CSV file on local machine because as shown in my example, I am looking to output in CSV, not store the output as a CSV file.

Best Answer

You can copy a query results directly into a CSV file, e.g.

copy (
    select id, field1 
    from (
        values (1, 'val1'), (2, 'val2')
        ) as v(id, field1)
) to '/data/test.csv' (format csv, header);

The file /data/test.csv:

id,field1
1,val1
2,val2

Use the function concat_ws() to get an output in expected format. Base types will be automatically converted to texts:

select concat_ws(', ', id, field1, date1, dec1)
from (
    values 
        (1::int, 'val1', '2017-01-01'::date, 1.1::dec), 
        (2::int, 'val2', '2017-01-02'::date, 1.2::dec)
    ) as v(id, field1, date1, dec1)

        concat_ws         
--------------------------
 1, val1, 2017-01-01, 1.1
 2, val2, 2017-01-02, 1.2
(2 rows)
Related Question