Re: Patch: FORCE_NULL option for copy COPY in CSV mode

On Sun, Sep 29, 2013 at 1:39 PM, Ian Lawrence Barwick <barwick(at)gmail(dot)com> wrote:
> Hi,
>
> This patch implements the following TODO item:
>
> Allow COPY in CSV mode to control whether a quoted zero-length
> string is treated as NULL
>
> Currently this is always treated as a zero-length string,
> which generates an error when loading into an integer column
>
> Re: [PATCHES] allow CSV quote in NULL
> http://archives.postgresql.org/pgsql-hackers/2007-07/msg00905.php
>
>
> http://wiki.postgresql.org/wiki/Todo#COPY
>
>
> I had a very definite use-case for this functionality recently while importing
> CSV files generated by Oracle, and was somewhat frustrated by the existence
> of a FORCE_NOT_NULL option for specific columns, but not one for
> FORCE_NULL.

While going through documentation of this patch to understand it's
usage, I found a small mistake.

+ Force the specified columns' values to be converted to <literal>NULL</>
+ if the value contains an empty string.

It seems quote after columns is wrong.

Also if your use case is to treat empty strings as NULL (as per above
documentation), can't it be handled with "WITH NULL AS" option.
For example, something like:

postgres=# COPY testnull FROM stdin with CSV NULL AS E'';
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
>> 50,
>> \.
postgres=# select * from testnull;
a | b
----+------
50 | NULL
(1 row)

With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com