public inbox for [email protected]help / color / mirror / Atom feed
COPY options 7+ messages / 3 participants [nested] [flat]
* COPY options @ 2016-04-28 16:58 Andrei M. Eichler <[email protected]> 0 siblings, 1 reply; 7+ messages in thread From: Andrei M. Eichler @ 2016-04-28 16:58 UTC (permalink / raw) To: pgsql-docs Hi everyone, In the documentation of COPY options, it lists the following options with a "_" in it's name: FORCE_QUOTE FORCE_NOT_NULL FORCE_NULL But when one tries to use it as it is from the docs, one gets the following results: ERROR: syntax error at or near "force_null" Shouldn't it be without the "_" in the docs? ^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: COPY options @ 2016-04-28 20:27 Kevin Grittner <[email protected]> parent: Andrei M. Eichler <[email protected]> 0 siblings, 1 reply; 7+ messages in thread From: Kevin Grittner @ 2016-04-28 20:27 UTC (permalink / raw) To: Andrei M. Eichler <[email protected]>; +Cc: pgsql-docs On Thu, Apr 28, 2016 at 11:58 AM, Andrei M. Eichler <[email protected]> wrote: > when one tries to use it as it is from the docs, one gets the following > results: > ERROR: syntax error at or near "force_null" Works for me. Please copy/paste the actual command and the actual, complete error message. https://wiki.postgresql.org/wiki/Guide_to_reporting_problems -- Kevin Grittner EDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-docs mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-docs ^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: COPY options @ 2016-04-29 15:31 Andrei M. Eichler <[email protected]> parent: Kevin Grittner <[email protected]> 0 siblings, 2 replies; 7+ messages in thread From: Andrei M. Eichler @ 2016-04-29 15:31 UTC (permalink / raw) To: Kevin Grittner <[email protected]>; +Cc: pgsql-docs Hi, thanks for the wiki link. I'm using Postgrees 9.5.2, running on Debian 8.4 I have a csv file with all columns quoted, even null values. Following the documentation, I wrote this COPY FROM command: COPY test_copy FROM '/mnt/disk1/files/test_file.csv' with csv header delimiter ';' force_null date_column, date_column2, date_column3, date_column4, date_column5 encoding 'latin1'; And received this error message: ERROR: syntax error at or near "force_null" LINE 1: ...test_file.csv' with csv header delimiter ';' force_null... To fix the error, I had to replace the "_" to " ", so "force_null" becomes "force null" : COPY test_copy FROM '/mnt/disk1/files/test_file.csv' with csv header delimiter ';' force null date_column, date_column2, date_column3, date_column4, date_column5 encoding 'latin1'; COPY 99999 In the docs (http://www.postgresql.org/docs/current/static/sql-copy.html) the "FORCE" options are written with "_" but the correct name is with " " 2016-04-28 17:27 GMT-03:00 Kevin Grittner <[email protected]>: > On Thu, Apr 28, 2016 at 11:58 AM, Andrei M. Eichler > <[email protected]> wrote: > > > when one tries to use it as it is from the docs, one gets the following > > results: > > ERROR: syntax error at or near "force_null" > > Works for me. > > Please copy/paste the actual command and the actual, complete error > message. > > https://wiki.postgresql.org/wiki/Guide_to_reporting_problems > > -- > Kevin Grittner > EDB: http://www.enterprisedb.com > The Enterprise PostgreSQL Company > ^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: COPY options @ 2016-04-29 15:50 Tom Lane <[email protected]> parent: Andrei M. Eichler <[email protected]> 1 sibling, 0 replies; 7+ messages in thread From: Tom Lane @ 2016-04-29 15:50 UTC (permalink / raw) To: Andrei M. Eichler <[email protected]>; +Cc: Kevin Grittner <[email protected]>; pgsql-docs "Andrei M. Eichler" <[email protected]> writes: > Following the documentation, I wrote this COPY FROM command: > COPY test_copy FROM '/mnt/disk1/files/test_file.csv' with csv header > delimiter ';' force_null date_column, date_column2, date_column3, > date_column4, date_column5 encoding 'latin1'; Which part of the documentation, exactly, inspired you to write that? Certainly not the main syntax summary for COPY, which would tell you to write something like with (format csv, header, ...); What you've got there is some variant of the pre-9.0 COPY syntax, which is documented at the bottom of the COPY reference page, and hopefully not used anywhere else. FORCE_NULL isn't mentioned in that syntax, and for that matter neither is FORCE NULL; if that works, it's accidental. regards, tom lane -- Sent via pgsql-docs mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-docs ^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: COPY options @ 2016-04-29 15:53 Kevin Grittner <[email protected]> parent: Andrei M. Eichler <[email protected]> 1 sibling, 1 reply; 7+ messages in thread From: Kevin Grittner @ 2016-04-29 15:53 UTC (permalink / raw) To: Andrei M. Eichler <[email protected]>; +Cc: pgsql-docs On Fri, Apr 29, 2016 at 10:31 AM, Andrei M. Eichler <[email protected]> wrote: > Following the documentation, I wrote this COPY FROM command: > > COPY test_copy FROM '/mnt/disk1/files/test_file.csv' with csv header > delimiter ';' force_null date_column, date_column2, date_column3, > date_column4, date_column5 encoding 'latin1'; The syntax synopsis in the current docs requires parentheses around any options. I was a little surprised that you got it to work at all without them; but then remembered there is older (deprecated and undocumented) syntax that was not ripped out to avoid breaking code which was working before the syntax change in 9.0. To do this per the current documentation (which I would strongly recommend over the old, undocumented, deprecated syntax), try this: COPY test_copy FROM '/mnt/disk1/files/test_file.csv' with ( format csv, header true, delimiter ';', force_null (date_column, date_column2, date_column3, date_column4, date_column5), encoding 'latin1' ); This new syntax was adopted to make it easier to add new options, and to avoid having to define new keywords or reserved words to do so. It's not really a question of whether we should go back to documenting the deprecated syntax, but whether (and when) support for it should finally be ripped out. It was last documented in 8.4. -- Kevin Grittner EDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-docs mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-docs ^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: COPY options @ 2016-04-29 16:03 Tom Lane <[email protected]> parent: Kevin Grittner <[email protected]> 0 siblings, 1 reply; 7+ messages in thread From: Tom Lane @ 2016-04-29 16:03 UTC (permalink / raw) To: Kevin Grittner <[email protected]>; +Cc: Andrei M. Eichler <[email protected]>; pgsql-docs Kevin Grittner <[email protected]> writes: > It's not really a question of whether we should go back to > documenting the deprecated syntax, but whether (and when) support > for it should finally be ripped out. It was last documented in 8.4. We still document, and support, not only the pre-9.0 COPY syntax but also the pre-7.3 COPY syntax; see the bottom of the COPY reference page. The timescale for getting rid of that kind of thing is *really* long, in part for fear of breaking old pg_dump backups. BTW, so far as FORCE NULL goes, I think that feature was implemented earlier in the 9.0 cycle than where we put in the new generalized option syntax. When we did the latter, we reverted the documentation of the old syntax to exactly match 8.4, but did not take out the gram.y production allowing the never-released FORCE NULL syntax. I do not recall if that was intentional or an oversight. We could (a) document it, (b) remove it, or (c) leave it alone. At this point I favor the benign neglect approach. regards, tom lane -- Sent via pgsql-docs mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-docs ^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: COPY options @ 2016-04-29 16:30 Andrei M. Eichler <[email protected]> parent: Tom Lane <[email protected]> 0 siblings, 0 replies; 7+ messages in thread From: Andrei M. Eichler @ 2016-04-29 16:30 UTC (permalink / raw) To: Tom Lane <[email protected]>; +Cc: Kevin Grittner <[email protected]>; pgsql-docs Thanks for the replies, I'm using postgres since 8.3 and didn't notice at the time of 9.0 release the change in the syntax, as the old syntax still works, every time I read the current documentation I didn't notice that I was using it wrong. I agree with Tom Lane about leaving it alone. Another question, I don't know if I should ask it here, why FORCE_NOT_NULL and FORCE_NULL doesn't allow * as list of columns as FORCE_QUOTE? 2016-04-29 13:03 GMT-03:00 Tom Lane <[email protected]>: > Kevin Grittner <[email protected]> writes: > > It's not really a question of whether we should go back to > > documenting the deprecated syntax, but whether (and when) support > > for it should finally be ripped out. It was last documented in 8.4. > > We still document, and support, not only the pre-9.0 COPY syntax but > also the pre-7.3 COPY syntax; see the bottom of the COPY reference page. > The timescale for getting rid of that kind of thing is *really* long, > in part for fear of breaking old pg_dump backups. > > BTW, so far as FORCE NULL goes, I think that feature was implemented > earlier in the 9.0 cycle than where we put in the new generalized > option syntax. When we did the latter, we reverted the documentation > of the old syntax to exactly match 8.4, but did not take out the > gram.y production allowing the never-released FORCE NULL syntax. > I do not recall if that was intentional or an oversight. > We could (a) document it, (b) remove it, or (c) leave it alone. > At this point I favor the benign neglect approach. > > regards, tom lane > ^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2016-04-29 16:30 UTC | newest] Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2016-04-28 16:58 COPY options Andrei M. Eichler <[email protected]> 2016-04-28 20:27 ` Kevin Grittner <[email protected]> 2016-04-29 15:31 ` Andrei M. Eichler <[email protected]> 2016-04-29 15:50 ` Tom Lane <[email protected]> 2016-04-29 15:53 ` Kevin Grittner <[email protected]> 2016-04-29 16:03 ` Tom Lane <[email protected]> 2016-04-29 16:30 ` Andrei M. Eichler <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox