How do I display rows as CSV?

Question

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.

Answer 1

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)

Answer 2

Using the record -> text cast

This method is documented as Composite Type Input and Output Syntax

1. More efficient and faster being written in C record_out
2. Supports rudimentary escaping and quoting
3. Supports casting back to record.

Here is an example,

SELECT trim(both '()' FROM t::text) AS csv
FROM ( VALUES
  ( 1,2,3,',' ),
  (2,3,4,'a')
) AS t(z,x,c);

    csv    
-----------
 1,2,3,","
 2,3,4,a
(2 rows)

The external text representation of a composite value consists of items that are interpreted according to the I/O conversion rules for the individual field types, plus decoration that indicates the composite structure. The decoration consists of parentheses (( and )) around the whole value, plus commas (,) between adjacent items. Whitespace outside the parentheses is ignored, but within the parentheses it is considered part of the field value, and might or might not be significant depending on the input conversion rules for the field data type. For example, in:

'( 42)'

the whitespace will be ignored if the field type is integer, but not if it is text.

As shown previously, when writing a composite value you can write double quotes around any individual field value. You must do so if the field value would otherwise confuse the composite-value parser. In particular, fields containing parentheses, commas, double quotes, or backslashes must be double-quoted. To put a double quote or backslash in a quoted composite field value, precede it with a backslash. (Also, a pair of double quotes within a double-quoted field value is taken to represent a double quote character, analogously to the rules for single quotes in SQL literal strings.) Alternatively, you can avoid quoting and use backslash-escaping to protect all data characters that would otherwise be taken as composite syntax.

A completely empty field value (no characters at all between the commas or parentheses) represents a NULL. To write a value that is an empty string rather than NULL, write "".

The composite output routine will put double quotes around field values if they are empty strings or contain parentheses, commas, double quotes, backslashes, or white space. (Doing so for white space is not essential, but aids legibility.) Double quotes and backslashes embedded in field values will be doubled.

I would never use this in production. This is a really bad idea but not for the method that I'm suggesting here. A SQL query returns it's result set with schema. Here you're losing all of that data, and concerning the database with formatting and display. You're also doing in it way that less tested and efficient than COPY, \COPY and pg_dump --data-only. My contention: I would always be doing this in the app layer with a library, or by dumping to a file/STDOUT with COPY. What is your plan when you need a JSON/XML output for the data or whatever?