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

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.