COPY vs \COPY FROM PROGRAM $$ quoting difference?
I found what seems to be an odd difference between COPY and \copy parsing.
I am using a bash pipeline of sed commands to clean up a source data file
before importing it into a table. This works fine when working from the
command line and piping the result to psql on STDIN.
However I attempted to put this same workflow into a psql script (as
opposed to a shell script). To avoid quoting issues with the shell pipeline
I put my pipeline command string in dollar quotes.
eg.
COPY dest_table FROM PROGRAM $$ sed 's/x/y/' | etc... $$
To my surprise this worked with COPY but not \COPY which failed with:
\copy: parse error at "$$"
Is this an undocumented difference? Is this even the appropriate email list
to ask this kind of question or report such a difference?
Thank you,
Alex Stoddard
On Fri, Sep 29, 2017 at 9:27 AM, Alexander Stoddard <
alexander.stoddard@gmail.com> wrote:
I found what seems to be an odd difference between COPY and \copy parsing.
[...]
COPY dest_table FROM PROGRAM $$ sed 's/x/y/' | etc... $$
To my surprise this worked with COPY but not \COPY which failed with:
\copy: parse error at "$$"Is this an undocumented difference? Is this even the appropriate email
list to ask this kind of question or report such a difference?
This is the correct place for seeking such clarification. The docs
cannot cover every possible thing people might do and these lists (-general
in particular) are here to fill in the gaps.
The negative condition that "psql" itself doesn't understand
dollar-quoting is not documented. Dollar-quoting is documented as a
server-interpreted SQL Syntax feature and only applies there.
While the commands are similar COPY is server-side SQL while \copy is a
psql meta-command that psql converts to SQL, executes, obtains the results,
and processes. Note that the server would never see "PROGRAM $$" since the
server would be unable to access the local program being referred to. The
server sees "FROM stdin" and psql feeds the results of the PROGRAM
invocation to the server over that pipe.
David J.
On Fri, Sep 29, 2017 at 11:54 AM, David G. Johnston <
david.g.johnston@gmail.com> wrote:
On Fri, Sep 29, 2017 at 9:27 AM, Alexander Stoddard <
alexander.stoddard@gmail.com> wrote:I found what seems to be an odd difference between COPY and \copy parsing.
[...]
COPY dest_table FROM PROGRAM $$ sed 's/x/y/' | etc... $$
To my surprise this worked with COPY but not \COPY which failed with:
\copy: parse error at "$$"Is this an undocumented difference? Is this even the appropriate email
list to ask this kind of question or report such a difference?This is the correct place for seeking such clarification. The docs
cannot cover every possible thing people might do and these lists (-general
in particular) are here to fill in the gaps.The negative condition that "psql" itself doesn't understand
dollar-quoting is not documented. Dollar-quoting is documented as a
server-interpreted SQL Syntax feature and only applies there.While the commands are similar COPY is server-side SQL while \copy is a
psql meta-command that psql converts to SQL, executes, obtains the results,
and processes. Note that the server would never see "PROGRAM $$" since the
server would be unable to access the local program being referred to. The
server sees "FROM stdin" and psql feeds the results of the PROGRAM
invocation to the server over that pipe.David J.
Thank you, David. That helps makes sense of everything. There is the
situation where psql is executed by a non-superuser on the server. But the
docs make clear that only STDOUT / STDIN, not not named files or commands
are allowed in that case. So I now realize I would just have been trading a
parse error for a security one had my dollar-quoting worked with \copy!