Re: [GENERAL] COPY ... FROM stdin WITH FORMAT csv

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