Re: [GENERAL] COPY ... FROM stdin WITH FORMAT csv
On Tue, Mar 21, 2017 at 10:31 AM, Alexander Farber < alexander.far...@gmail.com> wrote: > Good evening, > > 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: > [...] > > 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... > > [ [ WITH ] ( option [, ...] ) ] The above means the entire "WITH" section is optional, as is the word WITH. However, if you want to add "with" options they must appear within parentheses, those are not optional. Multiple options can appear within the single set of parentheses. """ FORMAT Selects the data format to be read or written: text, csv (Comma Separated Values), or binary. The default is text. """ Valid values for format are as listed, no single quote required (not sure about if they are allowed) Therefore: WITH (FORMAT csv) David J.
Re: [GENERAL] COPY ... FROM stdin WITH FORMAT csv
On 3/21/2017 10:31 AM, Alexander Farber wrote: 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... its just csv, not 'csv' ... And I have tried adding/removing commas at the end of lines too. if its getting a syntax error on the COPY command, its not yet read the data... note that COPY FROM STDIN doesn't actually read from stdin, it requires the data to be passed through to it with a special API (PQputCopyData() in libpq, or similar in other APIs). you can use \copy in psql to stream the data from the same input. -- john r pierce, recycling bits in santa cruz -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] COPY ... FROM stdin WITH FORMAT csv
Alexander: On Tue, Mar 21, 2017 at 6:31 PM, Alexander Farber 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@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] COPY ... FROM stdin WITH FORMAT csv
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. 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... ^
Re: [GENERAL] COPY ... FROM stdin WITH FORMAT csv
On Tuesday, March 21, 2017, Alexander Farber wrote: > > words=> COPY words_reviews (uid, author, nice, review, updated) FROM stdin > FORMAT csv; > What did you read that lead you to think the above shoud work? David J.
Re: [GENERAL] COPY ... FROM stdin WITH FORMAT csv
On 03/21/2017 12:21 PM, David G. Johnston wrote: > words=> COPY words_reviews (uid, author, nice, review, updated) FROM > stdin FORMAT csv; What did you read that lead you to think the above shoud work? I don't know about COPY FROM, but COPY TO works without parens (or FORMAT), like this: db=> copy (select 1+1, 2+2) to stdout with csv; 2,4 (tested on pg 9.5) I never use parens, so I was surprised to see in the docs and the replies here that they are necessary. Am I just exploiting a bug in the parser? Paul -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] COPY ... FROM stdin WITH FORMAT csv
On Tue, Mar 21, 2017 at 12:45 PM, Paul Jungwirth < p...@illuminatedcomputing.com> wrote: > On 03/21/2017 12:21 PM, David G. Johnston wrote: > >> > words=> COPY words_reviews (uid, author, nice, review, updated) FROM >> > stdin FORMAT csv; >> >> What did you read that lead you to think the above shoud work? >> > > I don't know about COPY FROM, but COPY TO works without parens (or > FORMAT), like this: > > db=> copy (select 1+1, 2+2) to stdout with csv; > 2,4 > > (tested on pg 9.5) > > I never use parens, so I was surprised to see in the docs and the replies > here that they are necessary. Am I just exploiting a bug in the parser? That's documented backward compatibility syntax: https://www.postgresql.org/docs/9.6/static/sql-copy.html bottom of the page. David J.
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.kla...@aklaver.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] COPY ... FROM stdin WITH FORMAT csv
On Tue, Mar 21, 2017 at 1:45 PM, Adrian Klaver 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.
Re: [GENERAL] COPY ... FROM stdin WITH FORMAT csv
Hi David, On Tue, Mar 21, 2017 at 8:21 PM, David G. Johnston < david.g.johns...@gmail.com> wrote: > > On Tuesday, March 21, 2017, Alexander Farber wrote: >> >> words=> COPY words_reviews (uid, author, nice, review, updated) FROM stdin FORMAT csv; > > > What did you read that lead you to think the above shoud work? > ok thanks, I had misunderstood your text - "The above means the entire "WITH" section is optional, as is the word WITH. However, if you want to add "with" options they must appear within parentheses, those are not optional. Multiple options can appear within the single set of parentheses." I think the root cause is that it is difficult to understand an english sentence trying to describe a keyword "with" :-) Regards Alex