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

On Sat, Oct 5, 2013 at 7:38 AM, Amit Kapila <amit(dot)kapila16(at)gmail(dot)com> wrote:
> 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.

That's a correct plural possessive in English, but in might be better
worded as "Force any empty string encountered in the input for the
specified columns to be interpreted as a NULL value."

> 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)

Good point. If this patch is just implementing something that can
already be done with another syntax, we don't need it.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company