From: | Francisco Olarte <folarte(at)peoplecall(dot)com> |
---|---|
To: | Alexander Farber <alexander(dot)farber(at)gmail(dot)com> |
Cc: | pgsql-general <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: COPY ... FROM stdin WITH FORMAT csv |
Date: | 2017-03-21 18:11:48 |
Message-ID: | CA+bJJbx0hwiQoxD-vY+2uH5Sfe5P9iGaeoMHtjHrtmDq3er2nQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Alexander:
On Tue, Mar 21, 2017 at 6:31 PM, Alexander Farber
<alexander(dot)farber(at)gmail(dot)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.
From | Date | Subject | |
---|---|---|---|
Next Message | Alexander Farber | 2017-03-21 19:11:53 | Re: COPY ... FROM stdin WITH FORMAT csv |
Previous Message | John R Pierce | 2017-03-21 17:51:26 | Re: COPY ... FROM stdin WITH FORMAT csv |