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

The post was made before I subscribed to the mailing list, so posting my
review separately. The link to the original patch mail is
http://www.postgresql.org/message-id/CAB8KJ=jS-Um4TGwenS5wLUfJK6K4rNOm_V6GRUj+tcKekL2=GQ@mail.gmail.com

> 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.
>
> I'll add this to the November commitfest.
>
>
> Regards
>
> Ian Barwick
>
>
==================
Contents & Purpose
==================

This patch introduces a new 'FORCE_NULL' option which has the opposite
functionality of the already present 'FORCE_NOT_NULL' option for the COPY
command. Prior to this option there was no way to convert a zeroed-length
quoted value to a NULL value when COPY FROM is used to import data from CSV
formatted files.

==================
Submission Review
==================

The patch is in the correct context diff format. It includes changes to the
documentation as well as additional regression tests. The description has
been discussed and defined in the previous mails leading to this patch.

=====================
Functionality Review
=====================

CORRECTION NEEDED - Due to a minor error in code (details in 'Code Review'
section below), force_null option is not limited to COPY FROM, and works
even when COPY TO is used. This should instead give an error message.

The updated documentation describes the added functionality clearly.

All regression tests passed successfully.

Code compilation after including patch was successful. No warnings either.

Manually tested COPY FROM with FORCE_NULL for a number of scenarios, all
with expected outputs. No issues.

Been testing the patch for a few days, no crashes or weird behavior
observed.

=============================================
Code Formatting Review (Needs Improvement)
=============================================

Looks good, the tabs between variable declaration and accompanying comment
can be improved.

=================================
Code Review (Needs Improvement)
=================================

1. There is a " NOTE: force_not_null option are not applied to the returned
fields." before COPY FROM block. Similar note should be added for
force_null option too.

2. One of the conditions to check and give an error if force_null is true
and copy from is false is wrong, cstate->force_null should be checked
instead of cstate->force_notnull:

/* Check force_notnull */
if (!cstate->csv_mode && cstate->force_notnull != NIL)
ereport(ERROR,
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
errmsg("COPY force not null available only
in CSV mode")));
if (cstate->force_notnull != NIL && !is_from)
ereport(ERROR,
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
errmsg("COPY force not null only available using
COPY FROM")));

/* Check force_null */
if (!cstate->csv_mode && cstate->force_null != NIL)
ereport(ERROR,
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
errmsg("COPY force null available only in
CSV mode")));

==> if (cstate->force_notnull != NIL && !is_from)
ereport(ERROR,
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
errmsg("COPY force null only available using COPY
FROM")));

===============================
Suggested Changes & Conclusion
===============================

The Above mentioned error condition should be corrected. Minor comments and
tab changes are upto the author.

In all, suggested modifications aside, the patch works well and in my
opinion, would be a useful addition to the COPY command.

Payal Singh,
OmniTi Computer Consulting Inc.
Junior Database Architect

2013-11-01 Payal Singh <payal(at)omniti(dot)com>:
> The post was made before I subscribed to the mailing list, so posting my
> review separately. The link to the original patch mail is
> http://www.postgresql.org/message-id/CAB8KJ=jS-Um4TGwenS5wLUfJK6K4rNOm_V6GRUj+tcKekL2=GQ@mail.gmail.com
>
>>
>> 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.
>>
>> I'll add this to the November commitfest.
>>
>>
>> Regards
>>
>> Ian Barwick
>
>
> ==================
> Contents & Purpose
> ==================
>
> This patch introduces a new 'FORCE_NULL' option which has the opposite
> functionality of the already present 'FORCE_NOT_NULL' option for the COPY
> command. Prior to this option there was no way to convert a zeroed-length
> quoted value to a NULL value when COPY FROM is used to import data from CSV
> formatted files.
>
> ==================
> Submission Review
> ==================
>
> The patch is in the correct context diff format. It includes changes to the
> documentation as well as additional regression tests. The description has
> been discussed and defined in the previous mails leading to this patch.
>
> =====================
> Functionality Review
> =====================
>
> CORRECTION NEEDED - Due to a minor error in code (details in 'Code Review'
> section below), force_null option is not limited to COPY FROM, and works
> even when COPY TO is used. This should instead give an error message.
>
> The updated documentation describes the added functionality clearly.
>
> All regression tests passed successfully.
>
> Code compilation after including patch was successful. No warnings either.
>
> Manually tested COPY FROM with FORCE_NULL for a number of scenarios, all
> with expected outputs. No issues.
>
> Been testing the patch for a few days, no crashes or weird behavior
> observed.
>
> =============================================
> Code Formatting Review (Needs Improvement)
> =============================================
>
> Looks good, the tabs between variable declaration and accompanying comment
> can be improved.
>
> =================================
> Code Review (Needs Improvement)
> =================================
>
> 1. There is a " NOTE: force_not_null option are not applied to the returned
> fields." before COPY FROM block. Similar note should be added for force_null
> option too.
>
> 2. One of the conditions to check and give an error if force_null is true
> and copy from is false is wrong, cstate->force_null should be checked
> instead of cstate->force_notnull:
>
> /* Check force_notnull */
> if (!cstate->csv_mode && cstate->force_notnull != NIL)
> ereport(ERROR,
> (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
> errmsg("COPY force not null available only
> in CSV mode")));
> if (cstate->force_notnull != NIL && !is_from)
> ereport(ERROR,
> (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
> errmsg("COPY force not null only available using
> COPY FROM")));
>
> /* Check force_null */
> if (!cstate->csv_mode && cstate->force_null != NIL)
> ereport(ERROR,
> (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
> errmsg("COPY force null available only in
> CSV mode")));
>
> ==> if (cstate->force_notnull != NIL && !is_from)
> ereport(ERROR,
> (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
> errmsg("COPY force null only available using COPY
> FROM")));
>
> ===============================
> Suggested Changes & Conclusion
> ===============================
>
> The Above mentioned error condition should be corrected. Minor comments and
> tab changes are upto the author.
>
> In all, suggested modifications aside, the patch works well and in my
> opinion, would be a useful addition to the COPY command.

Hi Payal

Many thanks for the review, and my apologies for not getting back to
you earlier.

Updated version of the patch attached with suggested corrections. I'm not
sure about the tabs in the variable declarations - the whole section
seems to be all over the place (regardless of whether tabs are set to 4 or
8 spaces) and could do with tidying up, however I didn't want to expand the
scope of the patch too much.

Quick recap of the reasons behind this patch - we had a bunch of CSV files
(and by "bunch" I mean totalling hundreds of millions of lines) exported
from a data source which was unable to distinguish between an empty string
and a null value. Too late we realized we had a ridiculous number of
comma separated rows with some empty strings which should be kept as such,
and some empty strings which should be imported as NULLs. "Wait", I said,
for I remembered COPY FROM had some kind of option involving specifying
the NULL status of certain columns. Alas it turned out to be the opposite
of what we required, and we found another workaround, but I thought as
it's likely we would face a similar situation in the future, it would
be handy to have the option available.

Example:

Given a table like this (a very stripped-down version of the original use
case):

CREATE TABLE nulltest (
prefix TEXT NOT NULL DEFAULT '',
altname TEXT DEFAULT NULL
);

I want to be able to do this:

postgres=# COPY nulltest FROM STDIN WITH (FORMAT CSV, FORCE_NULL (altname));
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
>> "",""
>> \.
postgres=# \pset null NULL
Null display (null) is "NULL".
postgres=# SELECT * FROM nulltest ;
prefix | altname
--------+---------
| NULL
(1 row)

I don't have any strong feelings about the syntax - as is it's just
the logical opposite of what's already available.

Regards

Ian Barwick

On 01/28/2014 05:55 AM, Ian Lawrence Barwick wrote:
>
> Hi Payal
>
> Many thanks for the review, and my apologies for not getting back to
> you earlier.
>
> Updated version of the patch attached with suggested corrections.

On a very quick glance, I see that you have still not made adjustments
to contrib/file_fdw to accommodate this new option. I don't see why this
COPY option should be different in that respect.

cheers

andrew

2014-01-29 Andrew Dunstan <andrew(at)dunslane(dot)net>:
>
> On 01/28/2014 05:55 AM, Ian Lawrence Barwick wrote:
>>
>>
>> Hi Payal
>>
>> Many thanks for the review, and my apologies for not getting back to
>> you earlier.
>>
>> Updated version of the patch attached with suggested corrections.
>
> On a very quick glance, I see that you have still not made adjustments to
> contrib/file_fdw to accommodate this new option. I don't see why this COPY
> option should be different in that respect.

Hmm, that idea seems to have escaped me completely. I'll get onto it forthwith.

Regards

Ian Barwick

2014/1/29 Ian Lawrence Barwick <barwick(at)gmail(dot)com>:
> 2014-01-29 Andrew Dunstan <andrew(at)dunslane(dot)net>:
>>
>> On 01/28/2014 05:55 AM, Ian Lawrence Barwick wrote:
>>>
>>>
>>> Hi Payal
>>>
>>> Many thanks for the review, and my apologies for not getting back to
>>> you earlier.
>>>
>>> Updated version of the patch attached with suggested corrections.
>>
>> On a very quick glance, I see that you have still not made adjustments to
>> contrib/file_fdw to accommodate this new option. I don't see why this COPY
>> option should be different in that respect.
>
> Hmm, that idea seems to have escaped me completely. I'll get onto it forthwith.

Striking while the keyboard is hot... version with contrib/file_fdw
modifications
attached.

Regards

Ian Barwick

On 01/29/2014 10:59 AM, Ian Lawrence Barwick wrote:
> 2014/1/29 Ian Lawrence Barwick <barwick(at)gmail(dot)com>:
>> 2014-01-29 Andrew Dunstan <andrew(at)dunslane(dot)net>:
>>> On 01/28/2014 05:55 AM, Ian Lawrence Barwick wrote:
>>>>
>>>> Hi Payal
>>>>
>>>> Many thanks for the review, and my apologies for not getting back to
>>>> you earlier.
>>>>
>>>> Updated version of the patch attached with suggested corrections.
>>> On a very quick glance, I see that you have still not made adjustments to
>>> contrib/file_fdw to accommodate this new option. I don't see why this COPY
>>> option should be different in that respect.
>> Hmm, that idea seems to have escaped me completely. I'll get onto it forthwith.
> Striking while the keyboard is hot... version with contrib/file_fdw
> modifications
> attached.
>
>

I have reviewed this. Generally it's good, but the author has made a
significant error - the idea is not to force a quoted empty string to
null, but to force a quoted null string to null, whatever the null
string might be. The default case has these the same, but if you specify
a non-empty null string they aren't.

That difference actually made the file_fdw regression results plain
wrong, in my view, in that they expected a quoted empty string to be
turned to null even when the null string was something else.

I've adjusted this and the docs and propose to apply the attached patch
in the next day or two unless there are any objections.

cheers

andrew

2014-03-02 8:26 GMT+09:00 Andrew Dunstan <andrew(at)dunslane(dot)net>:
>
> On 01/29/2014 10:59 AM, Ian Lawrence Barwick wrote:
>>
>> 2014/1/29 Ian Lawrence Barwick <barwick(at)gmail(dot)com>:
>>>
>>> 2014-01-29 Andrew Dunstan <andrew(at)dunslane(dot)net>:
>>>>
>>>> On 01/28/2014 05:55 AM, Ian Lawrence Barwick wrote:
>>>>>
>>>>>
>>>>> Hi Payal
>>>>>
>>>>> Many thanks for the review, and my apologies for not getting back to
>>>>> you earlier.
>>>>>
>>>>> Updated version of the patch attached with suggested corrections.
>>>>
>>>> On a very quick glance, I see that you have still not made adjustments
>>>> to
>>>> contrib/file_fdw to accommodate this new option. I don't see why this
>>>> COPY
>>>> option should be different in that respect.
>>>
>>> Hmm, that idea seems to have escaped me completely. I'll get onto it
>>> forthwith.
>>
>> Striking while the keyboard is hot... version with contrib/file_fdw
>> modifications
>> attached.
>>
>>

> I have reviewed this. Generally it's good, but the author has made a
> significant error - the idea is not to force a quoted empty string to null,
> but to force a quoted null string to null, whatever the null string might
> be. The default case has these the same, but if you specify a non-empty null
> string they aren't.

The author slaps himself on the forehead while regretting he was temporally
constricted when dealing with the patch and never thought to look beyond
the immediate use case.

Thanks for the update, much appreciated.

> That difference actually made the file_fdw regression results plain wrong,
> in my view, in that they expected a quoted empty string to be turned to null
> even when the null string was something else.
>
> I've adjusted this and the docs and propose to apply the attached patch in
> the next day or two unless there are any objections.

Unless I'm overlooking something, output from "SELECT * FROM text_csv;"
in 'output/file_fdw.source' still needs updating?

Regards

Ian Barwick

On 03/02/2014 10:06 PM, Ian Lawrence Barwick wrote:
> 2014-03-02 8:26 GMT+09:00 Andrew Dunstan <andrew(at)dunslane(dot)net>:
>> On 01/29/2014 10:59 AM, Ian Lawrence Barwick wrote:
>>> 2014/1/29 Ian Lawrence Barwick <barwick(at)gmail(dot)com>:
>>>> 2014-01-29 Andrew Dunstan <andrew(at)dunslane(dot)net>:
>>>>> On 01/28/2014 05:55 AM, Ian Lawrence Barwick wrote:
>>>>>>
>>>>>> Hi Payal
>>>>>>
>>>>>> Many thanks for the review, and my apologies for not getting back to
>>>>>> you earlier.
>>>>>>
>>>>>> Updated version of the patch attached with suggested corrections.
>>>>> On a very quick glance, I see that you have still not made adjustments
>>>>> to
>>>>> contrib/file_fdw to accommodate this new option. I don't see why this
>>>>> COPY
>>>>> option should be different in that respect.
>>>> Hmm, that idea seems to have escaped me completely. I'll get onto it
>>>> forthwith.
>>> Striking while the keyboard is hot... version with contrib/file_fdw
>>> modifications
>>> attached.
>>>
>>>
>> I have reviewed this. Generally it's good, but the author has made a
>> significant error - the idea is not to force a quoted empty string to null,
>> but to force a quoted null string to null, whatever the null string might
>> be. The default case has these the same, but if you specify a non-empty null
>> string they aren't.
> The author slaps himself on the forehead while regretting he was temporally
> constricted when dealing with the patch and never thought to look beyond
> the immediate use case.
>
> Thanks for the update, much appreciated.
>
>> That difference actually made the file_fdw regression results plain wrong,
>> in my view, in that they expected a quoted empty string to be turned to null
>> even when the null string was something else.
>>
>> I've adjusted this and the docs and propose to apply the attached patch in
>> the next day or two unless there are any objections.
> Unless I'm overlooking something, output from "SELECT * FROM text_csv;"
> in 'output/file_fdw.source' still needs updating?
>
>

Yes, you're right. Will fix.

cheers

andrew

On Mon, Mar 3, 2014 at 1:13 PM, Andrew Dunstan <andrew(at)dunslane(dot)net> wrote:
>>> That difference actually made the file_fdw regression results plain
>>> wrong,
>>> in my view, in that they expected a quoted empty string to be turned to
>>> null
>>> even when the null string was something else.
>>>
>>> I've adjusted this and the docs and propose to apply the attached patch
>>> in
>>> the next day or two unless there are any objections.
>>
>> Unless I'm overlooking something, output from "SELECT * FROM text_csv;"
>> in 'output/file_fdw.source' still needs updating?
While reading this patch, I found a small typo in copy2.[sql|out] =>
s/violiaton/violation/g
Regards,
--
Michael

On 03/03/2014 06:48 AM, Michael Paquier wrote:
> On Mon, Mar 3, 2014 at 1:13 PM, Andrew Dunstan <andrew(at)dunslane(dot)net> wrote:
>>>> That difference actually made the file_fdw regression results plain
>>>> wrong,
>>>> in my view, in that they expected a quoted empty string to be turned to
>>>> null
>>>> even when the null string was something else.
>>>>
>>>> I've adjusted this and the docs and propose to apply the attached patch
>>>> in
>>>> the next day or two unless there are any objections.
>>> Unless I'm overlooking something, output from "SELECT * FROM text_csv;"
>>> in 'output/file_fdw.source' still needs updating?
> While reading this patch, I found a small typo in copy2.[sql|out] =>
> s/violiaton/violation/g

I have picked this up and committed the patch. Thanks to all.

cheers

andrew

On Wed, Mar 5, 2014 at 7:44 AM, Andrew Dunstan <andrew(at)dunslane(dot)net> wrote:
> I have picked this up and committed the patch. Thanks to all.
Sorry for coming after the battle, but while looking at what has been
committed I noticed that copy2.sql is actually doing twice in a row
the same test:
COPY forcetest (a, b, c) FROM STDIN WITH (FORMAT csv,
FORCE_NOT_NULL(b), FORCE_NULL(c));
1,,""
\.
-- should succeed with no effect ("b" remains an empty string, "c" remains NULL)
COPY forcetest (a, b, c) FROM STDIN WITH (FORMAT csv,
FORCE_NOT_NULL(b), FORCE_NULL(c));
2,,""
\.

See? For both tests the quotes are placed on the same column, the 3rd.
I think that one of them should be like that, with the quotes on the
2nd column => 2,"",
The attached patch corrects that... and a misplaced comment.
Regards,
--
Michael

After testing this feature, I noticed that FORCE_NULL and
FORCE_NOT_NULL can both be specified with COPY on the same column.
This does not seem correct. The attached patch adds some more error
handling, and a regression test case for that.
Regards,
--
Michael

On 03/05/2014 09:11 AM, Michael Paquier wrote:
> After testing this feature, I noticed that FORCE_NULL and
> FORCE_NOT_NULL can both be specified with COPY on the same column.
> This does not seem correct. The attached patch adds some more error
> handling, and a regression test case for that.
>

Strictly they are not actually contradictory, since FORCE NULL relates
to quoted null strings and FORCE NOT NULL relates to unquoted null
strings. Arguably the docs are slightly loose on this point. Still,
applying both FORCE NULL and FORCE NOT NULL to the same column would be
rather perverse, since it would result in a quoted null string becoming
null and an unquoted null string becoming not null.

I'd be more inclined just to tighten the docs and maybe expand the
regression tests a bit, but I could be persuaded the other way if people
think it's worth it.

cheers

andrew

2014-03-05 23:27 GMT+09:00 Andrew Dunstan <andrew(at)dunslane(dot)net>:
>
> On 03/05/2014 09:11 AM, Michael Paquier wrote:
>>
>> After testing this feature, I noticed that FORCE_NULL and
>> FORCE_NOT_NULL can both be specified with COPY on the same column.
>> This does not seem correct. The attached patch adds some more error
>> handling, and a regression test case for that.
>>
>
>
> Strictly they are not actually contradictory, since FORCE NULL relates to
> quoted null strings and FORCE NOT NULL relates to unquoted null strings.
> Arguably the docs are slightly loose on this point. Still, applying both
> FORCE NULL and FORCE NOT NULL to the same column would be rather perverse,
> since it would result in a quoted null string becoming null and an unquoted
> null string becoming not null.

Too frazzled to recall clearly right now, but I think that was the somewhat
counterintuitive conclusion I originally came to.

> I'd be more inclined just to tighten the docs and maybe expand the
> regression tests a bit, but I could be persuaded the other way if people
> think it's worth it.

Might be worth doing if it's an essentially useless feature, if only to
preempt an unending chain of bug reports.

Many thanks for everyone's input on this, and apologies for not giving
the patch enough rigorous attention.

Regards

Ian Barwick

On Wed, Mar 5, 2014 at 11:37 PM, Ian Lawrence Barwick <barwick(at)gmail(dot)com> wrote:
> 2014-03-05 23:27 GMT+09:00 Andrew Dunstan <andrew(at)dunslane(dot)net>:
>>
>> On 03/05/2014 09:11 AM, Michael Paquier wrote:
>>>
>>> After testing this feature, I noticed that FORCE_NULL and
>>> FORCE_NOT_NULL can both be specified with COPY on the same column.
>>> This does not seem correct. The attached patch adds some more error
>>> handling, and a regression test case for that.
>>>
>>
>>
>> Strictly they are not actually contradictory, since FORCE NULL relates to
>> quoted null strings and FORCE NOT NULL relates to unquoted null strings.
>> Arguably the docs are slightly loose on this point. Still, applying both
>> FORCE NULL and FORCE NOT NULL to the same column would be rather perverse,
>> since it would result in a quoted null string becoming null and an unquoted
>> null string becoming not null.
>
> Too frazzled to recall clearly right now, but I think that was the somewhat
> counterintuitive conclusion I originally came to.
In this case I may be an intuitive guy :), but OK I see your point. So
if we specify both this produces the exact opposite as the default,
default being an empty string inserted for a quoted empty string and
NULL inserted for a non-quoted empty string. So yes I'm fine with a
note on the docs about that, and some more regression tests.

Btw, if we allow this behavior in COPY, why doesn't file_fdw allow
both options to be allowed on the same column for a foreign table?
Current behavior of file_fdw seems rather inconsistent with COPY as it
stands now.
--
Michael

On Wed, Mar 5, 2014 at 11:58 PM, Michael Paquier
<michael(dot)paquier(at)gmail(dot)com> wrote:
> So if we specify both this produces the exact opposite of the default,
> default being an empty string inserted for a quoted empty string and
> NULL inserted for a non-quoted empty string. So yes I'm fine with a
> note on the docs about that, and some more regression tests.

For people who did not get this one, here is a short example:
=# ¥pset null 'null'
Null display (null) is "null".
=# create table aa (a text);
CREATE TABLE
=# COPY aa FROM STDIN WITH (FORMAT csv);
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
>> ""
>>
>> \.
=# select * from aa;
a
------

null
(2 rows)
=# truncate aa;
TRUNCATE TABLE
Time: 12.149 ms
=# COPY aa FROM STDIN
WITH (FORMAT csv, FORCE_NULL(a), FORCE_NOT_NULL(a));
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
>> ""
>>
>> \.
Time: 3776.662 ms
=# select * from aa;
a
------
null

(2 rows)
--
Michael

Andrew Dunstan <andrew(at)dunslane(dot)net> writes:
> On 03/05/2014 09:11 AM, Michael Paquier wrote:
>> After testing this feature, I noticed that FORCE_NULL and
>> FORCE_NOT_NULL can both be specified with COPY on the same column.

> Strictly they are not actually contradictory, since FORCE NULL relates
> to quoted null strings and FORCE NOT NULL relates to unquoted null
> strings. Arguably the docs are slightly loose on this point. Still,
> applying both FORCE NULL and FORCE NOT NULL to the same column would be
> rather perverse, since it would result in a quoted null string becoming
> null and an unquoted null string becoming not null.

Given the remarkable lack of standardization of "CSV" output, who's
to say that there might not be data sources out there for which this
is the desired behavior? It's weird, I agree, but I think throwing
an error for the combination is not going to be helpful. It's not
like somebody might accidentally write both on the same column.

+1 for clarifying the docs, though, more or less in the words you
used above.

regards, tom lane

On Thu, Mar 6, 2014 at 12:09 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Andrew Dunstan <andrew(at)dunslane(dot)net> writes:
>> On 03/05/2014 09:11 AM, Michael Paquier wrote:
>>> After testing this feature, I noticed that FORCE_NULL and
>>> FORCE_NOT_NULL can both be specified with COPY on the same column.
>
>> Strictly they are not actually contradictory, since FORCE NULL relates
>> to quoted null strings and FORCE NOT NULL relates to unquoted null
>> strings. Arguably the docs are slightly loose on this point. Still,
>> applying both FORCE NULL and FORCE NOT NULL to the same column would be
>> rather perverse, since it would result in a quoted null string becoming
>> null and an unquoted null string becoming not null.
>
> Given the remarkable lack of standardization of "CSV" output, who's
> to say that there might not be data sources out there for which this
> is the desired behavior? It's weird, I agree, but I think throwing
> an error for the combination is not going to be helpful. It's not
> like somebody might accidentally write both on the same column.
>
> +1 for clarifying the docs, though, more or less in the words you
> used above.
Following that, I have hacked the patch attached to update the docs
with an additional regression test (actually replaces a test that was
the same as the one before in copy2).

I am attaching as well a second patch for file_fdw, to allow the use
of force_null and force_not_null on the same column, to be consistent
with COPY.
Regards,
--
Michael

On Wed, Mar 5, 2014 at 09:49:30PM +0900, Michael Paquier wrote:
> On Wed, Mar 5, 2014 at 7:44 AM, Andrew Dunstan <andrew(at)dunslane(dot)net> wrote:
> > I have picked this up and committed the patch. Thanks to all.
> Sorry for coming after the battle, but while looking at what has been
> committed I noticed that copy2.sql is actually doing twice in a row
> the same test:
> COPY forcetest (a, b, c) FROM STDIN WITH (FORMAT csv,
> FORCE_NOT_NULL(b), FORCE_NULL(c));
> 1,,""
> \.
> -- should succeed with no effect ("b" remains an empty string, "c" remains NULL)
> COPY forcetest (a, b, c) FROM STDIN WITH (FORMAT csv,
> FORCE_NOT_NULL(b), FORCE_NULL(c));
> 2,,""
> \.
>
> See? For both tests the quotes are placed on the same column, the 3rd.
> I think that one of them should be like that, with the quotes on the
> 2nd column => 2,"",
> The attached patch corrects that... and a misplaced comment.
> Regards,

Thanks. Patch applied.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ Everyone has their own god. +

On Fri, Mar 7, 2014 at 05:08:54PM +0900, Michael Paquier wrote:
> On Thu, Mar 6, 2014 at 12:09 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> > Andrew Dunstan <andrew(at)dunslane(dot)net> writes:
> >> On 03/05/2014 09:11 AM, Michael Paquier wrote:
> >>> After testing this feature, I noticed that FORCE_NULL and
> >>> FORCE_NOT_NULL can both be specified with COPY on the same column.
> >
> >> Strictly they are not actually contradictory, since FORCE NULL relates
> >> to quoted null strings and FORCE NOT NULL relates to unquoted null
> >> strings. Arguably the docs are slightly loose on this point. Still,
> >> applying both FORCE NULL and FORCE NOT NULL to the same column would be
> >> rather perverse, since it would result in a quoted null string becoming
> >> null and an unquoted null string becoming not null.
> >
> > Given the remarkable lack of standardization of "CSV" output, who's
> > to say that there might not be data sources out there for which this
> > is the desired behavior? It's weird, I agree, but I think throwing
> > an error for the combination is not going to be helpful. It's not
> > like somebody might accidentally write both on the same column.
> >
> > +1 for clarifying the docs, though, more or less in the words you
> > used above.
> Following that, I have hacked the patch attached to update the docs
> with an additional regression test (actually replaces a test that was
> the same as the one before in copy2).
>
> I am attaching as well a second patch for file_fdw, to allow the use
> of force_null and force_not_null on the same column, to be consistent
> with COPY.
> Regards,

Correction, this is the patch applied, not the earlier version.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ Everyone has their own god. +

On Wed, Apr 23, 2014 at 5:07 AM, Bruce Momjian <bruce(at)momjian(dot)us> wrote:

> On Fri, Mar 7, 2014 at 05:08:54PM +0900, Michael Paquier wrote:
> > On Thu, Mar 6, 2014 at 12:09 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> > > Andrew Dunstan <andrew(at)dunslane(dot)net> writes:
> > >> On 03/05/2014 09:11 AM, Michael Paquier wrote:
> > >>> After testing this feature, I noticed that FORCE_NULL and
> > >>> FORCE_NOT_NULL can both be specified with COPY on the same column.
> > >
> > >> Strictly they are not actually contradictory, since FORCE NULL relates
> > >> to quoted null strings and FORCE NOT NULL relates to unquoted null
> > >> strings. Arguably the docs are slightly loose on this point. Still,
> > >> applying both FORCE NULL and FORCE NOT NULL to the same column would
> be
> > >> rather perverse, since it would result in a quoted null string
> becoming
> > >> null and an unquoted null string becoming not null.
> > >
> > > Given the remarkable lack of standardization of "CSV" output, who's
> > > to say that there might not be data sources out there for which this
> > > is the desired behavior? It's weird, I agree, but I think throwing
> > > an error for the combination is not going to be helpful. It's not
> > > like somebody might accidentally write both on the same column.
> > >
> > > +1 for clarifying the docs, though, more or less in the words you
> > > used above.
> > Following that, I have hacked the patch attached to update the docs
> > with an additional regression test (actually replaces a test that was
> > the same as the one before in copy2).
> >
> > I am attaching as well a second patch for file_fdw, to allow the use
> > of force_null and force_not_null on the same column, to be consistent
> > with COPY.
> > Regards,
>
> Correction, this is the patch applied, not the earlier version.
>
Thanks for taking the time to look at that.
--
Michael