Anyone can comment on a bug. Have a simpler test case? Does it
work for you on a different platform? Let us know!
Just going to say 'Me too!'? Don't clutter the database with that please
!

Your email address:MUST BE VALID

Solve the problem:37 - 20 = ?

Subscribe to this entry?

[2016-11-11 13:44 UTC] cward at titanhq dot com

Description:
------------
pg_copy_to generates incorrect SQL for the Postgresql COPY TO statement.
For instance:
pg_copy_to($connection, 'test');
generates:
COPY test TO STDOUT DELIMITERS E' ' WITH NULL AS E'\\N'
"DELIMITERS" is incorrect; it should be "DELIMITER". See Postgresql manual:
https://www.postgresql.org/docs/current/static/sql-copy.html#AEN77298
However, this defect is only apparent if you try to use pg_copy_to on a view, which is perfectly legal as documented here:
https://www.postgresql.org/docs/current/static/sql-copy.html#AEN77520
Test script:
---------------
pg_copy_to($connection, '(select * from test'));
Expected result:
----------------
Return the contents of the view into a formatted array
Actual result:
--------------
ERROR: syntax error at or near "DELIMITERS" at character 129

History

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?

[2016-11-21 11:45 UTC] cward at titanhq dot com

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:
https://www.postgresql.org/docs/current/static/sql-copy.html#AEN77789
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'