go to bug id or search bugs for
pg_copy_to generates incorrect SQL for the Postgresql COPY TO statement.
COPY test TO STDOUT DELIMITERS E' ' WITH NULL AS E'\\N'
"DELIMITERS" is incorrect; it should be "DELIMITER". See Postgresql manual:
However, this defect is only apparent if you try to use pg_copy_to on a view, which is perfectly legal as documented here:
pg_copy_to($connection, '(select * from test'));
Return the contents of the view into a formatted array
ERROR: syntax error at or near "DELIMITERS" at character 129
Add a Patch
Add a Pull Request
The postgres docs also say that `DELIMITERS` is still supported, could the error message be misleading and the actual error be caused by some of the surrounding syntax?
The 'DELIMITERS' syntax is still supported, but that syntax is from a much older version of Postgresql (7.2), which was limited to using COPY TO on a table only:
The newer syntax would allow the use of a query or a table name as the data source. However, it requires using 'DELIMITER', and also moving the 'WITH' to earlier in the expression (or removing it entirely).
I have manually tested the queries generated by pg_copy_to. This query form will not work in any version of postgres:
COPY (select * from test) TO STDOUT DELIMITERS E' ' WITH NULL AS E'\\N'
However, this one would work on any postgres version from 8.2 onwards, with a query or a table:
COPY (select * from test) TO STDOUT WITH DELIMITER E' ' NULL AS E'\\N'
COPY test TO STDOUT WITH DELIMITER E' ' NULL AS E'\\N'
Automatic comment on behalf of email@example.com
Log: Fix bug #73498