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.
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