sqlpostgresqlpsqldump

psql how to copy output of query with single whitespace between columns


I want to create update commands from a table (using select queries) like so:

SELECT 'UPDATE table1 SET col=', col, 'WHERE id=', id, ';' FROM table1;

I want to output this query to stdout with COPY. I am doing this:

COPY (SELECT 'UPDATE table1 SET col=', col, 'WHERE id=', id FROM table1) TO STDOUT DELIMITER ' ';

The problem is that this is outputing the columns separated by \ not just whitespace:

UPDATE\ table1\ SET\ col=23 WHERE\ id= 345;

How can I just output columns with single whitespaces in between.


Solution

  • The idiomatic way to produce SQL with SQL queries is through the format function, producing the query in a single column, like this:

    SELECT format('UPDATE table1 SET col=%s WHERE id=%s;'
                  ,col, id) FROM table1;
    

    The format specifier %L can be used instead of %s to have properly quoted literals, in order to avoid SQL injection (or %I for identifiers like table names or column names).

    The query being in a single column, you won't have the problem of the separator between columns.

    That being said, it's not clear why you see backslashes before spaces when using DELIMITER ' ' with COPY, because COPY itself doesn't do that. Example:

    COPY (select 'ab', 'cd') TO STDOUT delimiter ' ';
    

    results in:

    ab cd