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@momjian.us>          http://momjian.us
  EnterpriseDB                               http://www.enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +