php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login

go to bug id or search bugs for

Bug #73498 Incorrect SQL generated for pg_copy_to()
Submitted: 2016-11-11 13:44 UTC Modified: 2016-11-21 11:45 UTC
From: cward at titanhq dot com Assigned:
Status: Closed Package: PostgreSQL related
PHP Version: 5.6.28 OS: FreeBSD
Private report: No CVE-ID: None
View Developer Edit
 [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

Patches

Pull Requests

Pull requests:

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2016-11-20 20:54 UTC] php at duncanc dot co dot uk 
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'
 [2016-11-21 23:48 UTC] ab@php.net 
Automatic comment on behalf of git@duncanc.co.uk
Revision: http://git.php.net/?p=php-src.git;a=commit;h=644e290fcdc228d1990d8010c807dc39af6de4d5
Log: Fix bug #73498
 [2016-11-21 23:48 UTC] ab@php.net
-Status: Open +Status: Closed
 [2016-11-30 23:14 UTC] davey@php.net 
Automatic comment on behalf of git@duncanc.co.uk
Revision: http://git.php.net/?p=php-src.git;a=commit;h=644e290fcdc228d1990d8010c807dc39af6de4d5
Log: Fix bug #73498
 
PHP Copyright © 2001-2024 The PHP Group
All rights reserved. 		Last updated: Thu Nov 21 11:01:29 2024 UTC