Обсуждение: [GENERAL] COPY ... FROM stdin WITH FORMAT csv

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

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.

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

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

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