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

On 10/09/2013 03:25 PM, Robert Haas wrote:
> Therefore, a user who wants the opposite of the default behavior -
> namely, unquoted empty strings as empty strings and quoted empty
> strings as nulls - should specify both FORCE NULL and FORCE NOT NULL.

Is there a real world example of this case? How common is it? And how
come I haven't heard of it in the nine or so years since we've been
supporting CSV import?

Frankly it strikes me as more than a little perverse to have a CSV file
where you want an unquoted empty string to map to emptystring but a
quoted one to map to null, especially if you want that for the same
field. The main two problems I have heard people people complain about are:

* some CSV producers quote everything, even NULL values. This is
particularly true where the producer doesn't treat NULLs the same
way we do. This patch will fix that, allowing you to get a null on a
column by column basis as desired.
* some tables have NOT NULL constraints that are violated by unquoted
empty strings being interpreted as NULL - that was fixed long ago by
FORCE NOT NULL.

It's faintly possible you might encounter both of these problems in some
form in the one file, but not very likely.

Regarding syntax suggestions - this is not a green field. If we were
designing the syntax of COPY from scratch today we might make other
decisions than those that were made back in 2004. But I don't think we
can add new options using a quite different style from what's already
been done. That would look more than odd.

cheers

andrew