From: | Bruce Momjian <bruce(at)momjian(dot)us> |
---|---|
To: | Stephen Frost <sfrost(at)snowman(dot)net> |
Cc: | pgsql-patches(at)postgresql(dot)org |
Subject: | Re: allow CSV quote in NULL |
Date: | 2007-09-26 08:38:54 |
Message-ID: | 200709260838.l8Q8csB11970@momjian.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers pgsql-patches |
This has been saved for the 8.4 release:
http://momjian.postgresql.org/cgi-bin/pgpatches_hold
---------------------------------------------------------------------------
Stephen Frost wrote:
> Greetings,
>
> Please find attached a minor patch to remove the constraints that a
> user can't include the delimiter or quote characters in a 'NULL AS'
> string when importing CSV files.
>
> This allows a user to explicitly request that NULL conversion happen
> on fields which are quoted. As the quote character is being allowed
> to be in the 'NULL AS' string now, there's no reason to exclude the
> delimiter character from being seen in that string as well, though
> unless quoted using the CSV quote character it won't ever be matched.
>
> An example of the usage:
>
> sfrost*=# \copy billing_data from ~/BillingSamplePricerFile.csv
> with csv header quote as '"' null as '""'
>
> This is no contrived example, it's an issue I ran into earlier today
> when I got a file which had (for reasons unknown to me and not easily
> changed upstream):
>
> "1","V","WASHDCABC12","","120033"...
>
> Both of the ending columns shown are integer fields, the "" here being
> used to indicate a NULL value.
>
> Without the patch, an ERROR occurs:
>
> sfrost=> \copy billing_data from ~/BillingSamplePricerFile.csv
> with csv header quote as '"'
> ERROR: invalid input syntax for integer: ""
>
> And there's no way to get it to import with COPY CSV mode. The
> patch adds this ability without affecting existing usage or changing
> the syntax. Even with the patch an ERROR occurs with the default
> treatment of CSV files:
>
> sfrost=# \copy billing_data from ~/BillingSamplePricerFile.csv
> with csv header quote as '"'
> ERROR: invalid input syntax for integer: ""
>
> Which would be expected. If the file is modified to remove the ""s
> for NULL columns, it imports just fine with the syntax above.
>
> It'd be really nice to have this included.
>
> Thanks!
>
> Stephen
[ Attachment, skipping... ]
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq
--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://www.enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
From | Date | Subject | |
---|---|---|---|
Next Message | Bruce Momjian | 2007-09-26 08:52:35 | Re: [HACKERS] Include Lists for Text Search |
Previous Message | Bruce Momjian | 2007-09-26 08:37:33 | Re: pgcrypto & strong ciphers limitation |
From | Date | Subject | |
---|---|---|---|
Next Message | Bruce Momjian | 2007-09-26 08:48:11 | Re: strpos() && KMP |
Previous Message | Bruce Momjian | 2007-09-26 08:36:30 | Re: Reviewing new index types (was Re: [PATCHES] Updated bitmap indexpatch) |