COPY ... FROM stdin WITH FORMAT csv

On Tue, Mar 21, 2017 at 10:31 AM, Alexander Farber <
alexander.farber@gmail.com> wrote:

Good evening,

I keep rereading https://www.postgresql.org/docs/9.6/static/sql-copy.html
but just can't figure the proper syntax to put some records into the table:
​[...]​

words=> COPY words_reviews (uid, author, nice, review, updated) FROM stdin
WITH FORMAT 'csv';
ERROR: syntax error at or near "FORMAT"
LINE 1: ...d, author, nice, review, updated) FROM stdin WITH FORMAT 'cs...

​​[ [ WITH ] ( option [, ...] ) ]

The above means the entire "WITH" section is optional, as is the word
WITH. However, if you want to add "with" options they must appear within
parentheses, those are not optional. Multiple options can appear within
the single set of parentheses.

"""
FORMAT
Selects the data format to be read or written: text, csv (Comma Separated
Values), or binary. The default is text.
"""

Valid values for format are as listed, no single quote required (not sure
about if they are allowed)

Therefore:

WITH (FORMAT csv)

David J.

On 3/21/2017 10:31 AM, Alexander Farber wrote:

words=> COPY words_reviews (uid, author, nice, review, updated) FROM
stdin WITH FORMAT 'csv';
ERROR: syntax error at or near "FORMAT"
LINE 1: ...d, author, nice, review, updated) FROM stdin WITH FORMAT 'cs...

its just csv, not 'csv' ...

And I have tried adding/removing commas at the end of lines too.

if its getting a syntax error on the COPY command, its not yet read the
data...

note that COPY .... FROM STDIN doesn't actually read from stdin, it
requires the data to be passed through to it with a special API
(PQputCopyData() in libpq, or similar in other APIs). you can use
\copy in psql to stream the data from the same input.

--
john r pierce, recycling bits in santa cruz

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Alexander:

On Tue, Mar 21, 2017 at 6:31 PM, Alexander Farber
<alexander.farber@gmail.com> wrote:

I keep rereading https://www.postgresql.org/docs/9.6/static/sql-copy.html
but just can't figure the proper syntax to put some records into the table:

It's not that complex, let's see....

words=> COPY words_reviews (uid, author, nice, review, updated) FROM stdin
WITH FORMAT 'csv';
ERROR: syntax error at or near "FORMAT"
LINE 1: ...d, author, nice, review, updated) FROM stdin WITH FORMAT 'cs...
^

Here you have an error ( more on this later ) so the next lines are
going to be interpreted as a new command....

words=> 1,2,1,'1 is nice by 2','2017-03-01',

Which you can clearly see because the prompt is => , initial, not ->,
continuation.

words-> 1,3,1,'1 is nice by 3','2017-03-02',
words-> 1,4,1,'1 is nice by 4','2017-03-03',
words-> 2,1,1,'2 is nice by 1','2017-03-01',
words-> 2,3,1,'2 is nice by 3','2017-03-02',

Also, you are putting an extra , at the end of the lines. This means
you have an empty string at the end, one extra fields. I do not
remember now if it hurts, but better omit it.

words-> 2,4,0,'2 is not nice by 4','2017-03-03'
words-> \.
Invalid command \.. Try \? for help.

All the lines up to here are considered part of the previous sql (
remember => vs -> ) command. You are not in copy mode, so psql tries
to interpret '\.' as a meta command ( like \d ) but fails.

words-> ;
ERROR: syntax error at or near "1"
LINE 1: 1,2,1,'1 is nice by 2','2017-03-01',
^

And here you terminate the SQL command, so it fails ( note it referes
to the first error, the initial line with => ).

I am not sure if FORMAT csv or FORMAT 'csv' should be used.

That is easy, try both. BUT! if you read the manual with care you will
notive it is "with ( options )", not "with options", so you are
missing parenthesis:

web_tarotpagos_staging=# create temporary table t(a varchar, b varchar);
CREATE TABLE

-- NO parens, no luck:
web_tarotpagos_staging=# copy t(a,b) from stdin with format csv;
ERROR: syntax error at or near "format"
LINE 1: copy t(a,b) from stdin with format csv;
^
web_tarotpagos_staging=# copy t(a,b) from stdin with format 'csv';
ERROR: syntax error at or near "format"
LINE 1: copy t(a,b) from stdin with format 'csv';

BUT, as soon as I put them:
^
web_tarotpagos_staging=# copy t(a,b) from stdin with (format csv);
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.

\.

Note the enter data approach. Also note copy is safe to try as you can
just abort it.

And I have tried adding/removing commas at the end of lines too.

That is commented above.

I have looked at pg_dump output, but it does not use csv.

pg_dump uses the default text format, a little more difficult but
vastly superior ( IMNSHO ) to CSV. It ( by default ) separates records
with newlines and fields with tab, and escapes newlines, tabs and
backslashes in data with backslash, so the transformation is
contextless, much easier than csv:

Copy out: Replace NULL with '\N', newline with '\n', tab with '\t',
backslash with '\\', join fields with tab, print with newline at end.

Copy In: Read till newline, split on tabs, replace '\n' with newline,
'\t' with tab, '\\' with backslash.

Much easier to get right than CSV ( how do you encode the C string ",;
\n\"\n\t; \t\"\'" ? )

Francisco Olarte.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Thank you - this has worked:

COPY words_reviews (uid, author, nice, review, updated) FROM stdin WITH
(FORMAT csv);
1,2,1,'1 is nice by 2','2017-03-01'
1,3,1,'1 is nice by 3','2017-03-02'
1,4,1,'1 is nice by 4','2017-03-03'
2,1,1,'2 is nice by 1','2017-03-01'
2,3,1,'2 is nice by 3','2017-03-02'
2,4,0,'2 is not nice by 4','2017-03-03'
\.

but I am confused about the comments that I should use \copy and not just
COPY and also that I could leave out WITH and brackets.

Because please take a look at the 9.6.2 psql output (COPY works, and
leaving out WITH brackets - not):

words=> COPY words_reviews (uid, author, nice, review, updated) FROM stdin
WITH (FORMAT csv);
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.

1,2,1,'1 is nice by 2','2017-03-01'

1,3,1,'1 is nice by 3','2017-03-02'
1,4,1,'1 is nice by 4','2017-03-03'
2,1,1,'2 is nice by 1','2017-03-01'
2,3,1,'2 is nice by 3','2017-03-02'
2,4,0,'2 is not nice by 4','2017-03-03'
\.

COPY 6

words=> COPY words_reviews (uid, author, nice, review, updated) FROM stdin
FORMAT csv;
ERROR: syntax error at or near "FORMAT"
LINE 1: ...s (uid, author, nice, review, updated) FROM stdin FORMAT csv...
^

On Tuesday, March 21, 2017, Alexander Farber <alexander.farber@gmail.com>
wrote:

words=> COPY words_reviews (uid, author, nice, review, updated) FROM stdin
FORMAT csv;

What did you read that lead you to think the above shoud work?

David J.

On 03/21/2017 12:21 PM, David G. Johnston wrote:

words=> COPY words_reviews (uid, author, nice, review, updated) FROM
stdin FORMAT csv;

What did you read that lead you to think the above shoud work?

I don't know about COPY FROM, but COPY TO works without parens (or
FORMAT), like this:

db=> copy (select 1+1, 2+2) to stdout with csv;

2,4

(tested on pg 9.5)

I never use parens, so I was surprised to see in the docs and the
replies here that they are necessary. Am I just exploiting a bug in the
parser?

Paul

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Tue, Mar 21, 2017 at 12:45 PM, Paul Jungwirth <
pj@illuminatedcomputing.com> wrote:

On 03/21/2017 12:21 PM, David G. Johnston wrote:

words=> COPY words_reviews (uid, author, nice, review, updated) FROM
stdin FORMAT csv;

What did you read that lead you to think the above shoud work?

I don't know about COPY FROM, but COPY TO works without parens (or
FORMAT), like this:

db=> copy (select 1+1, 2+2) to stdout with csv;
2,4

(tested on pg 9.5)

I never use parens, so I was surprised to see in the docs and the replies
here that they are necessary. Am I just exploiting a bug in the parser?

That's documented backward compatibility syntax:

https://www.postgresql.org/docs/9.6/static/sql-copy.html

bottom of the page.

David J.
​

On 03/21/2017 12:11 PM, Alexander Farber wrote:

Thank you - this has worked:

COPY words_reviews (uid, author, nice, review, updated) FROM stdin WITH
(FORMAT csv);
1,2,1,'1 is nice by 2','2017-03-01'
1,3,1,'1 is nice by 3','2017-03-02'
1,4,1,'1 is nice by 4','2017-03-03'
2,1,1,'2 is nice by 1','2017-03-01'
2,3,1,'2 is nice by 3','2017-03-02'
2,4,0,'2 is not nice by 4','2017-03-03'
\.

but I am confused about the comments that I should use \copy and not
just COPY and also that I could leave out WITH and brackets.

The difference between COPY and \copy is explained here:

https://www.postgresql.org/docs/9.6/static/app-psql.html

\copy ....

Basically COPY runs as the server user and so the files it uses have to
be accessible by the user the server runs as. \copy is a psql meta
command that runs as local user so it can access files the local user
can see and has privileges on.

Because please take a look at the 9.6.2 psql output (COPY works, and
leaving out WITH brackets - not):

words=> COPY words_reviews (uid, author, nice, review, updated) FROM
stdin WITH (FORMAT csv);
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.

1,2,1,'1 is nice by 2','2017-03-01'

1,3,1,'1 is nice by 3','2017-03-02'
1,4,1,'1 is nice by 4','2017-03-03'
2,1,1,'2 is nice by 1','2017-03-01'
2,3,1,'2 is nice by 3','2017-03-02'
2,4,0,'2 is not nice by 4','2017-03-03'
\.

COPY 6

words=> COPY words_reviews (uid, author, nice, review, updated) FROM
stdin FORMAT csv;
ERROR: syntax error at or near "FORMAT"
LINE 1: ...s (uid, author, nice, review, updated) FROM stdin FORMAT csv...
^

--
Adrian Klaver
adrian.klaver@aklaver.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Tue, Mar 21, 2017 at 1:45 PM, Adrian Klaver <adrian.klaver@aklaver.com>
wrote:

On 03/21/2017 12:11 PM, Alexander Farber wrote:

Thank you - this has worked:

COPY words_reviews (uid, author, nice, review, updated) FROM stdin WITH
(FORMAT csv);
1,2,1,'1 is nice by 2','2017-03-01'
1,3,1,'1 is nice by 3','2017-03-02'
1,4,1,'1 is nice by 4','2017-03-03'
2,1,1,'2 is nice by 1','2017-03-01'
2,3,1,'2 is nice by 3','2017-03-02'
2,4,0,'2 is not nice by 4','2017-03-03'
\.

but I am confused about the comments that I should use \copy and not
just COPY and also that I could leave out WITH and brackets.

The difference between COPY and \copy is explained here:

https://www.postgresql.org/docs/9.6/static/app-psql.html

\copy ....

Basically COPY runs as the server user and so the files it uses have to be
accessible by the user the server runs as. \copy is a psql meta command
that runs as local user so it can access files the local user can see and
has privileges on.

​I take it, then, if one chooses not to use pre-existing files and instead
inline the content as shown here, the choice between \copy and COPY becomes
a matter of style and not correctness. As a matter of style using \copy
makes it clear that everything that is needed to make the command work
exists locally.

The advantage to using "COPY FROM|TO stdin|stdout" is that the copy command
itself could (not tested) be spread out over multiple lines - which
especially for COPY TO can be advantageous. The entire \copy meta-command
cannot be split up.

David J.

Hi David,

On Tue, Mar 21, 2017 at 8:21 PM, David G. Johnston <
david.g.johnston@gmail.com> wrote:

On Tuesday, March 21, 2017, Alexander Farber <alexander.farber@gmail.com>

wrote:

words=> COPY words_reviews (uid, author, nice, review, updated) FROM

stdin FORMAT csv;

What did you read that lead you to think the above shoud work?

ok thanks, I had misunderstood your text -

"The above means the entire "WITH" section is optional, as is the word
WITH. However, if you want to add "with" options they must appear within
parentheses, those are not optional. Multiple options can appear within
the single set of parentheses."

I think the root cause is that it is difficult to understand an english
sentence trying to describe a keyword "with" :-)

Regards
Alex