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

Поиск
Список
Период
Сортировка
От David G. Johnston
Тема Re: [GENERAL] COPY ... FROM stdin WITH FORMAT csv
Дата
Msg-id CAKFQuwaU+6=g4-E3jcf=BnEUavn-wcSjxppXoLk9dMJ-WtcPzg@mail.gmail.com
обсуждение исходный текст
Ответ на Re: [GENERAL] COPY ... FROM stdin WITH FORMAT csv  (Adrian Klaver <adrian.klaver@aklaver.com>)
Список 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.

В списке pgsql-general по дате отправления:

Предыдущее
От: Adrian Klaver
Дата:
Сообщение: Re: [GENERAL] COPY ... FROM stdin WITH FORMAT csv
Следующее
От: Tom Ekberg
Дата:
Сообщение: [GENERAL] mysql_config_editor feature suggestion