Alexander: On Tue, Mar 21, 2017 at 6:31 PM, Alexander Farber <alexander.farber@xxxxxxxxx> 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. -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
