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
Welcome back! If you're the original bug submitter, here's where you can edit the bug or add additional notes.
If you forgot your password, you can retrieve your password here.
Password:
Status:
Package:
Bug Type:
Summary:
From: cward at titanhq dot com
New email:
PHP Version: OS:

 

 [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: Sat Sep 21 01:01:27 2024 UTC