Re: allow CSV quote in NULL

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. +