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

Question

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.

Answer 1

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