Postgres Pro
Downloads
Home   >   mailing lists

Thread: SQL Commands COPY

SQL Commands COPY

From
PG Doc comments form
Date:
The following documentation comment has been logged on the website:

Page: https://www.postgresql.org/docs/13/sql-copy.html
Description:

The synopsis and parameters for the COPY command show the FORCE_QUOTE option
with an underscore and requires column names to be enclosed with round
brackets, but the example in the compatibility section shows the option name
as FORCE QUOTE (no underscore) and has no requirement to enclose the column
names with round brackets.

When using psql (version 13.3) against a version 13 cluster, the method
shown in the compatibility section works whereas the methods shown in the
synopsis and parameters sections do not work and will throw a syntax
error.

My usage is
COPY (query)
TO STDOUT
CSV HEADER
DELIMITER E'\t'
NULL ''
QUOTE '"'
FORCE QUOTE column_name [, ...]

Re: SQL Commands COPY

From
Laurenz Albe
Date:
On Wed, 2021-06-09 at 19:54 +0000, PG Doc comments form wrote:
> Page: https://www.postgresql.org/docs/13/sql-copy.html
> Description:
> 
> The synopsis and parameters for the COPY command show the FORCE_QUOTE option
> with an underscore and requires column names to be enclosed with round
> brackets, but the example in the compatibility section shows the option name
> as FORCE QUOTE (no underscore) and has no requirement to enclose the column
> names with round brackets.
> 
> When using psql (version 13.3) against a version 13 cluster, the method
> shown in the compatibility section works whereas the methods shown in the
> synopsis and parameters sections do not work and will throw a syntax
> error.

You must be misreading something, the new syntax works:

COPY (SELECT 42 AS x) TO STDOUT (FORMAT 'csv', FORCE_QUOTE (x));

Yours,
Laurenz Albe

Re: SQL Commands COPY

From
Tom Lane
Date:
Laurenz Albe <laurenz.albe@cybertec.at> writes:
> On Wed, 2021-06-09 at 19:54 +0000, PG Doc comments form wrote:
>> When using psql (version 13.3) against a version 13 cluster, the method
>> shown in the compatibility section works whereas the methods shown in the
>> synopsis and parameters sections do not work and will throw a syntax
>> error.

> You must be misreading something, the new syntax works:
> COPY (SELECT 42 AS x) TO STDOUT (FORMAT 'csv', FORCE_QUOTE (x));

The most probable explanation seems to be that the OP is actually
connecting to an 8.4 (or older) PG server.

            regards, tom lane

Re: SQL Commands COPY

From
Neil Bower
Date:
It was a picnic. I was confusing the 9+ version syntax with the pre-9 version syntax at the bottom of the page. Most of the options between the two versions are the same and the pre-9 version still works with the newer versions of PostgreSQL.

I was connecting to a version 13 server at the time. I've been using this same syntax for several years and never noticed the one key difference between the older and newer versions of the syntax of having to enclose the options within round brackets until Laurenz's example. 

Thanks!

On Wed, 9 Jun 2021 at 15:47, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Laurenz Albe <laurenz.albe@cybertec.at> writes:
> On Wed, 2021-06-09 at 19:54 +0000, PG Doc comments form wrote:
>> When using psql (version 13.3) against a version 13 cluster, the method
>> shown in the compatibility section works whereas the methods shown in the
>> synopsis and parameters sections do not work and will throw a syntax
>> error.

> You must be misreading something, the new syntax works:
> COPY (SELECT 42 AS x) TO STDOUT (FORMAT 'csv', FORCE_QUOTE (x));

The most probable explanation seems to be that the OP is actually
connecting to an 8.4 (or older) PG server.

                        regards, tom lane