Thread: Switching from OSX to Linux, multi-line queries in \copy don't work anymore

I recently switched from OSX to Linux and \copy in psql no longer
accepts multi-line queries. For instance:

\copy (
    select
        *
    from
        pg_settings
) to '/tmp/settings.csv' with csv header

This works fine on OSX. On Linux I get:
\copy: parse error at end of line

Am I missing something here?

Version information:

OSX (installed from macports):
iDarwin huxley.local 10.8.0 Darwin Kernel Version 10.8.0: Tue Jun  7 16:32:41 PDT 2011;
root:xnu-1504.15.3~1/RELEASE_X86_64x86_64 
PostgreSQL 9.0.7 on x86_64-apple-darwin10.8.0, compiled by GCC i686-apple-darwin10-gcc-4.2.1 (GCC) 4.2.1 (Apple Inc.
build5666) (dot 3), 64-bit 

Linux (from pitti's ppa):
Linux planck 2.6.38-8-server #42-Ubuntu SMP Mon Apr 11 03:49:04 UTC 2011 x86_64 x86_64 x86_64 GNU/Linux
PostgreSQL 9.0.5 on x86_64-pc-linux-gnu, compiled by GCC gcc-4.5.real (Ubuntu/Linaro 4.5.2-8ubuntu4) 4.5.2, 64-bit

-Ryan Kelly

On 07/27/2012 09:28 PM, Ryan Kelly wrote:
> I recently switched from OSX to Linux and \copy in psql no longer
> accepts multi-line queries. For instance:
>
> \copy (
>      select
>          *
>      from
>          pg_settings
> ) to '/tmp/settings.csv' with csv header
>
> This works fine on OSX. On Linux I get:
> \copy: parse error at end of line
>
> Am I missing something here?
A wild guess: I'd say this is a consequence of the fact that psql on OS
X uses libedit, on Linux it uses readline.

Personally I had no idea that multiline \copy was possible at all. I've
always though the way backslash commands are EOL-terminated while
everything else is semicolon terminated is a bit of a wart, though.

I don't have an answer for you. Using the --no-readline argument makes
no difference on my 9.1.4 here. This may just be an area where libedit
is smarter than readline - or it might be that I'm totally wrong and the
real issue is something else entirely.

Thanks for making the effort to produce a good post with all the
detailed version info, exact error text, etc.

--
Craig Ringer

Craig Ringer wrote:
> On 07/27/2012 09:28 PM, Ryan Kelly wrote:
>> I recently switched from OSX to Linux and \copy in psql no longer
>> accepts multi-line queries. For instance:
>>
>> \copy (
>>      select
>>          *
>>      from
>>          pg_settings
>> ) to '/tmp/settings.csv' with csv header
>>
>> This works fine on OSX. On Linux I get:
>> \copy: parse error at end of line
>>
>> Am I missing something here?
> A wild guess: I'd say this is a consequence of the fact that psql on OS
> X uses libedit, on Linux it uses readline.
>
> Personally I had no idea that multiline \copy was possible at all. I've
> always though the way backslash commands are EOL-terminated while
> everything else is semicolon terminated is a bit of a wart, though.
>
> I don't have an answer for you. Using the --no-readline argument makes
> no difference on my 9.1.4 here. This may just be an area where libedit
> is smarter than readline - or it might be that I'm totally wrong and the
> real issue is something else entirely.
>
> Thanks for making the effort to produce a good post with all the
> detailed version info, exact error text, etc.

Also appear to get it here on single-line queries:

markMLl=> \copy (select * from pg_settings) to '/tmp/settings.csv' with
csv header;
\copy: parse error at "select"
markMLl=>

However my psql and server are rather old which could be an issue
(8.1.19 to server 8.4 if I recall correctly).

--
Mark Morgan Lloyd
markMLl .AT. telemetry.co .DOT. uk

[Opinions above are the author's, not those of his employers or colleagues]

On Fri, Jul 27, 2012 at 09:49:06PM +0800, Craig Ringer wrote:
> On 07/27/2012 09:28 PM, Ryan Kelly wrote:
> >I recently switched from OSX to Linux and \copy in psql no longer
> >accepts multi-line queries. For instance:
> >
> >\copy (
> >     select
> >         *
> >     from
> >         pg_settings
> >) to '/tmp/settings.csv' with csv header
> >
> >This works fine on OSX. On Linux I get:
> >\copy: parse error at end of line
> >
> >Am I missing something here?
> A wild guess: I'd say this is a consequence of the fact that psql on
> OS X uses libedit, on Linux it uses readline.
My psql on OSX is built with readline:
ryan@huxley: otool -L psql
/opt/local/lib/postgresql90/bin/psql:
        /opt/local/lib/postgresql90/libpq.5.dylib (compatibility version 5.0.0, current version 5.3.0)
        /opt/local/lib/libssl.1.0.0.dylib (compatibility version 1.0.0, current version 1.0.0)
        /opt/local/lib/libreadline.6.2.dylib (compatibility version 6.0.0, current version 6.2.0)
        /usr/lib/libSystem.B.dylib (compatibility version 1.0.0, current version 125.2.11)

Also note that I don't usually type these in directly, rather I use \e
to write them in vim. Doesn't make any difference, though.

> Personally I had no idea that multiline \copy was possible at all.
> I've always though the way backslash commands are EOL-terminated
> while everything else is semicolon terminated is a bit of a wart,
> though.
>
> I don't have an answer for you. Using the --no-readline argument
> makes no difference on my 9.1.4 here. This may just be an area where
> libedit is smarter than readline - or it might be that I'm totally
> wrong and the real issue is something else entirely.
>
> Thanks for making the effort to produce a good post with all the
> detailed version info, exact error text, etc.
>
> --
> Craig Ringer
>

-Ryan

On Fri, Jul 27, 2012 at 02:06:01PM +0000, Mark Morgan Lloyd wrote:
> Craig Ringer wrote:
> >On 07/27/2012 09:28 PM, Ryan Kelly wrote:
> >>I recently switched from OSX to Linux and \copy in psql no longer
> >>accepts multi-line queries. For instance:
> >>
> >>\copy (
> >>     select
> >>         *
> >>     from
> >>         pg_settings
> >>) to '/tmp/settings.csv' with csv header
> >>
> >>This works fine on OSX. On Linux I get:
> >>\copy: parse error at end of line
> >>
> >>Am I missing something here?
> >A wild guess: I'd say this is a consequence of the fact that psql
> >on OS X uses libedit, on Linux it uses readline.
> >
> >Personally I had no idea that multiline \copy was possible at all.
> >I've always though the way backslash commands are EOL-terminated
> >while everything else is semicolon terminated is a bit of a wart,
> >though.
> >
> >I don't have an answer for you. Using the --no-readline argument
> >makes no difference on my 9.1.4 here. This may just be an area
> >where libedit is smarter than readline - or it might be that I'm
> >totally wrong and the real issue is something else entirely.
> >
> >Thanks for making the effort to produce a good post with all the
> >detailed version info, exact error text, etc.
>
> Also appear to get it here on single-line queries:
>
> markMLl=> \copy (select * from pg_settings) to '/tmp/settings.csv'
> with csv header;
> \copy: parse error at "select"
> markMLl=>
>
> However my psql and server are rather old which could be an issue
> (8.1.19 to server 8.4 if I recall correctly).
This works for me, it might be the case that your psql is too old.
Perhaps \copy didn't support queries until later?

-Ryan

Ryan Kelly <rpkelly22@gmail.com> writes:
> I recently switched from OSX to Linux and \copy in psql no longer
> accepts multi-line queries. For instance:

> \copy (
>     select
>         *
>     from
>         pg_settings
> ) to '/tmp/settings.csv' with csv header

> This works fine on OSX. On Linux I get:
> \copy: parse error at end of line

FWIW, I get that error on either OS X or Linux, and I'm a bit astonished
by your report that there are any versions of psql that allow it.
psql doesn't do multi-line backslash commands, in any context.  Are you
sure you weren't doing a plain SQL "copy" command, without a backslash?

            regards, tom lane

On Fri, Jul 27, 2012 at 12:06 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Ryan Kelly <rpkelly22@gmail.com> writes:
>> I recently switched from OSX to Linux and \copy in psql no longer
>> accepts multi-line queries. For instance:
>
>> \copy (
>>     select
>>         *
>>     from
>>         pg_settings
>> ) to '/tmp/settings.csv' with csv header
>
>> This works fine on OSX. On Linux I get:
>> \copy: parse error at end of line
>
> FWIW, I get that error on either OS X or Linux, and I'm a bit astonished
> by your report that there are any versions of psql that allow it.
> psql doesn't do multi-line backslash commands, in any context.  Are you
> sure you weren't doing a plain SQL "copy" command, without a backslash?

it can be coerced:

postgres=# \copy (
  select 0
  ) to stdout
0

now -- to do that, I had to use the \e command to do it in vi, then
recall the command with readline :-).  \copy can work arbitrarily work
or fail in all kinds of ways.

merlin