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

2014-04-22 Thread Bruce Momjian
On Wed, Mar  5, 2014 at 09:49:30PM +0900, Michael Paquier wrote:
 On Wed, Mar 5, 2014 at 7:44 AM, Andrew Dunstan and...@dunslane.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  br...@momjian.ushttp://momjian.us
  EnterpriseDB http://enterprisedb.com

  + Everyone has their own god. +


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


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

2014-04-22 Thread Bruce Momjian
On Fri, Mar  7, 2014 at 05:08:54PM +0900, Michael Paquier wrote:
 On Thu, Mar 6, 2014 at 12:09 AM, Tom Lane t...@sss.pgh.pa.us wrote:
  Andrew Dunstan and...@dunslane.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  br...@momjian.ushttp://momjian.us
  EnterpriseDB http://enterprisedb.com

  + Everyone has their own god. +


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


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

2014-04-22 Thread Michael Paquier
On Wed, Apr 23, 2014 at 5:07 AM, Bruce Momjian br...@momjian.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 t...@sss.pgh.pa.us wrote:
   Andrew Dunstan and...@dunslane.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


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

2014-03-07 Thread Michael Paquier
On Thu, Mar 6, 2014 at 12:09 AM, Tom Lane t...@sss.pgh.pa.us wrote:
 Andrew Dunstan and...@dunslane.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
diff --git a/contrib/file_fdw/file_fdw.c b/contrib/file_fdw/file_fdw.c
index 7fb1dbc..97a35d0 100644
--- a/contrib/file_fdw/file_fdw.c
+++ b/contrib/file_fdw/file_fdw.c
@@ -267,11 +267,6 @@ file_fdw_validator(PG_FUNCTION_ARGS)
 		(errcode(ERRCODE_SYNTAX_ERROR),
 		 errmsg(conflicting or redundant options),
 		 errhint(option \force_not_null\ supplied more than once for a column)));
-			if(force_null)
-ereport(ERROR,
-		(errcode(ERRCODE_SYNTAX_ERROR),
-		 errmsg(conflicting or redundant options),
-		 errhint(option \force_not_null\ cannot be used together with \force_null\)));
 			force_not_null = def;
 			/* Don't care what the value is, as long as it's a legal boolean */
 			(void) defGetBoolean(def);
@@ -284,11 +279,6 @@ file_fdw_validator(PG_FUNCTION_ARGS)
 		(errcode(ERRCODE_SYNTAX_ERROR),
 		 errmsg(conflicting or redundant options),
 		 errhint(option \force_null\ supplied more than once for a column)));
-			if(force_not_null)
-ereport(ERROR,
-		(errcode(ERRCODE_SYNTAX_ERROR),
-		 errmsg(conflicting or redundant options),
-		 errhint(option \force_null\ cannot be used together with \force_not_null\)));
 			force_null = def;
 			(void) defGetBoolean(def);
 		}
diff --git a/contrib/file_fdw/input/file_fdw.source b/contrib/file_fdw/input/file_fdw.source
index 0c278aa..b608372 100644
--- a/contrib/file_fdw/input/file_fdw.source
+++ b/contrib/file_fdw/input/file_fdw.source
@@ -91,24 +91,22 @@ ALTER FOREIGN TABLE text_csv OPTIONS (SET format 'csv');
 \pset null _null_
 SELECT * FROM text_csv;
 
+-- force_not_null and force_null can be used together on the same column
+ALTER FOREIGN TABLE text_csv ALTER COLUMN word1 OPTIONS (force_null 'true');
+ALTER FOREIGN TABLE text_csv ALTER COLUMN word3 OPTIONS (force_not_null 'true');
+
 -- force_not_null is not allowed to be specified at any foreign object level:
 ALTER FOREIGN DATA WRAPPER file_fdw OPTIONS (ADD force_not_null '*'); -- ERROR
 ALTER SERVER file_server OPTIONS (ADD force_not_null '*'); -- ERROR
 CREATE USER MAPPING FOR public SERVER file_server OPTIONS (force_not_null '*'); -- ERROR
 CREATE FOREIGN TABLE tbl () SERVER file_server OPTIONS (force_not_null '*'); -- ERROR
 
--- force_not_null cannot be specified together with force_null
-ALTER FOREIGN TABLE text_csv ALTER COLUMN word1 OPTIONS (force_null 'true'); --ERROR
-
 -- force_null is not allowed to be specified at any foreign object level:
 ALTER FOREIGN DATA WRAPPER file_fdw OPTIONS (ADD force_null '*'); -- ERROR
 ALTER SERVER file_server OPTIONS (ADD force_null '*'); -- ERROR
 CREATE USER MAPPING FOR public SERVER file_server OPTIONS (force_null '*'); -- ERROR
 CREATE FOREIGN TABLE tbl () SERVER file_server OPTIONS (force_null '*'); -- ERROR
 
--- force_null cannot be specified together with force_not_null
-ALTER FOREIGN TABLE text_csv ALTER COLUMN word3 OPTIONS (force_not_null 'true'); --ERROR
-
 -- basic query tests
 SELECT * FROM agg_text WHERE b  10.0 ORDER BY a;
 SELECT * FROM agg_csv ORDER BY a;
diff --git a/contrib/file_fdw/output/file_fdw.source b/contrib/file_fdw/output/file_fdw.source
index 2bec160..bc183b8 100644
--- a/contrib/file_fdw/output/file_fdw.source
+++ b/contrib/file_fdw/output/file_fdw.source
@@ -115,6 +115,9 @@ SELECT * FROM text_csv;
  ABC   | abc|| 
 (5 rows)
 
+-- force_not_null and force_null can be used together on the same column
+ALTER FOREIGN TABLE text_csv ALTER COLUMN word1 OPTIONS (force_null 'true');
+ALTER FOREIGN TABLE text_csv ALTER COLUMN word3 OPTIONS 

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

2014-03-05 Thread Michael Paquier
On Wed, Mar 5, 2014 at 7:44 AM, Andrew Dunstan and...@dunslane.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
diff --git a/src/test/regress/expected/copy2.out 
b/src/test/regress/expected/copy2.out
index 76dea28..e8fb3d1 100644
--- a/src/test/regress/expected/copy2.out
+++ b/src/test/regress/expected/copy2.out
@@ -383,7 +383,6 @@ SELECT * FROM vistest;
 (2 rows)
 
 -- Test FORCE_NOT_NULL and FORCE_NULL options
--- should succeed with b set to an empty string and c set to NULL
 CREATE TEMP TABLE forcetest (
 a INT NOT NULL,
 b TEXT NOT NULL,
@@ -392,6 +391,7 @@ CREATE TEMP TABLE forcetest (
 e TEXT
 );
 \pset null NULL
+-- should succeed with b set to an empty string and c set to NULL
 BEGIN;
 COPY forcetest (a, b, c) FROM STDIN WITH (FORMAT csv, FORCE_NOT_NULL(b), 
FORCE_NULL(c));
 COMMIT;
diff --git a/src/test/regress/sql/copy2.sql b/src/test/regress/sql/copy2.sql
index e2be21f..63332bd 100644
--- a/src/test/regress/sql/copy2.sql
+++ b/src/test/regress/sql/copy2.sql
@@ -271,7 +271,6 @@ SELECT * FROM vistest;
 COMMIT;
 SELECT * FROM vistest;
 -- Test FORCE_NOT_NULL and FORCE_NULL options
--- should succeed with b set to an empty string and c set to NULL
 CREATE TEMP TABLE forcetest (
 a INT NOT NULL,
 b TEXT NOT NULL,
@@ -280,6 +279,7 @@ CREATE TEMP TABLE forcetest (
 e TEXT
 );
 \pset null NULL
+-- should succeed with b set to an empty string and c set to NULL
 BEGIN;
 COPY forcetest (a, b, c) FROM STDIN WITH (FORMAT csv, FORCE_NOT_NULL(b), 
FORCE_NULL(c));
 1,,
@@ -289,7 +289,7 @@ SELECT b, c FROM forcetest WHERE a = 1;
 -- should succeed with no effect (b remains an empty string, c remains 
NULL)
 BEGIN;
 COPY forcetest (a, b, c) FROM STDIN WITH (FORMAT csv, FORCE_NOT_NULL(b), 
FORCE_NULL(c));
-2,,
+2,,
 \.
 COMMIT;
 SELECT b, c FROM forcetest WHERE a = 2;

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


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

2014-03-05 Thread Michael Paquier
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
diff --git a/src/backend/commands/copy.c b/src/backend/commands/copy.c
index 70ee7e5..540da91 100644
--- a/src/backend/commands/copy.c
+++ b/src/backend/commands/copy.c
@@ -1457,6 +1457,26 @@ BeginCopy(bool is_from,
}
}
 
+   /*
+* Check if both force_null and force_not_null are used on the same
+* columns.
+*/
+   if (cstate-force_null  cstate-force_notnull)
+   {
+   int i;
+
+   for (i = 0; i  num_phys_attrs; i++)
+   {
+   if (cstate-force_notnull_flags[i] 
+   cstate-force_null_flags[i])
+   ereport(ERROR,
+   (errcode(ERRCODE_SYNTAX_ERROR),
+errmsg(conflicting or 
redundant options),
+errhint(\force_not_null\ 
and \force_null\ specified for the same column \%s\,
+
NameStr(tupDesc-attrs[i]-attname;
+   }
+   }
+
/* Use client encoding when ENCODING option is not specified. */
if (cstate-file_encoding  0)
cstate-file_encoding = pg_get_client_encoding();
diff --git a/src/test/regress/expected/copy2.out 
b/src/test/regress/expected/copy2.out
index 76dea28..5341b09 100644
--- a/src/test/regress/expected/copy2.out
+++ b/src/test/regress/expected/copy2.out
@@ -418,6 +418,12 @@ ERROR:  null value in column b violates not-null 
constraint
 DETAIL:  Failing row contains (3, null, , null, null).
 CONTEXT:  COPY forcetest, line 1: 3,,
 ROLLBACK;
+-- FORCE_NULL and FORCE_NOT_NULL cannot be used on the same column
+BEGIN;
+COPY forcetest (a) FROM STDIN WITH (FORMAT csv, FORCE_NULL(a), 
FORCE_NOT_NULL(a));
+ERROR:  conflicting or redundant options
+HINT:  force_not_null and force_null specified for the same column a
+ROLLBACK;
 -- should fail with not referenced by COPY error
 BEGIN;
 COPY forcetest (d, e) FROM STDIN WITH (FORMAT csv, FORCE_NOT_NULL(b));
diff --git a/src/test/regress/sql/copy2.sql b/src/test/regress/sql/copy2.sql
index e2be21f..91dc902 100644
--- a/src/test/regress/sql/copy2.sql
+++ b/src/test/regress/sql/copy2.sql
@@ -299,6 +299,10 @@ COPY forcetest (a, b, c) FROM STDIN WITH (FORMAT csv, 
FORCE_NULL(b), FORCE_NOT_N
 3,,
 \.
 ROLLBACK;
+-- FORCE_NULL and FORCE_NOT_NULL cannot be used on the same column
+BEGIN;
+COPY forcetest (a) FROM STDIN WITH (FORMAT csv, FORCE_NULL(a), 
FORCE_NOT_NULL(a));
+ROLLBACK;
 -- should fail with not referenced by COPY error
 BEGIN;
 COPY forcetest (d, e) FROM STDIN WITH (FORMAT csv, FORCE_NOT_NULL(b));

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


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

2014-03-05 Thread Andrew Dunstan


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


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


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

2014-03-05 Thread Ian Lawrence Barwick
2014-03-05 23:27 GMT+09:00 Andrew Dunstan and...@dunslane.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


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


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

2014-03-05 Thread Michael Paquier
On Wed, Mar 5, 2014 at 11:37 PM, Ian Lawrence Barwick barw...@gmail.com wrote:
 2014-03-05 23:27 GMT+09:00 Andrew Dunstan and...@dunslane.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


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


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

2014-03-05 Thread Michael Paquier
On Wed, Mar 5, 2014 at 11:58 PM, Michael Paquier
michael.paqu...@gmail.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


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


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

2014-03-05 Thread Tom Lane
Andrew Dunstan and...@dunslane.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


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


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

2014-03-04 Thread Andrew Dunstan


On 03/03/2014 06:48 AM, Michael Paquier wrote:

On Mon, Mar 3, 2014 at 1:13 PM, Andrew Dunstan and...@dunslane.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





--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


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

2014-03-03 Thread Michael Paquier
On Mon, Mar 3, 2014 at 1:13 PM, Andrew Dunstan and...@dunslane.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


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


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

2014-03-02 Thread Ian Lawrence Barwick
2014-03-02 8:26 GMT+09:00 Andrew Dunstan and...@dunslane.net:

 On 01/29/2014 10:59 AM, Ian Lawrence Barwick wrote:

 2014/1/29 Ian Lawrence Barwick barw...@gmail.com:

 2014-01-29 Andrew Dunstan and...@dunslane.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
diff --git a/contrib/file_fdw/data/text.csv b/contrib/file_fdw/data/text.csv
new file mode 100644
index ed348a9..f55d9cf
*** a/contrib/file_fdw/data/text.csv
--- b/contrib/file_fdw/data/text.csv
***
*** 1,4 
! AAA,aaa
! XYZ,xyz
! NULL,NULL
! ABC,abc
--- 1,5 
! AAA,aaa,123,
! XYZ,xyz,,321
! NULL,NULL,NULL,NULL
! NULL,NULL,NULL,NULL
! ABC,abc,,
diff --git a/contrib/file_fdw/file_fdw.c b/contrib/file_fdw/file_fdw.c
new file mode 100644
index 5639f4d..7fb1dbc
*** a/contrib/file_fdw/file_fdw.c
--- b/contrib/file_fdw/file_fdw.c
*** struct FileFdwOption
*** 48,56 
  
  /*
   * Valid options for file_fdw.
!  * These options are based on the options for COPY FROM command.
!  * But note that force_not_null is handled as a boolean option attached to
!  * each column, not as a table option.
   *
   * Note: If you are adding new option for user mapping, you need to modify
   * fileGetOptions(), which currently doesn't bother to look at user mappings.
--- 48,56 
  
  /*
   * Valid options for file_fdw.
!  * These options are based on the options for the COPY FROM command.
!  * But note that force_not_null and force_null are handled as boolean options
!  * attached to a column, not as table options.
   *
   * Note: If you are adding new option for user mapping, you need to modify
   * fileGetOptions(), which currently doesn't bother to look at user mappings.
*** static const struct FileFdwOption valid_
*** 69,75 
  	{null, ForeignTableRelationId},
  	{encoding, ForeignTableRelationId},
  	{force_not_null, AttributeRelationId},
! 
  	/*
  	 * force_quote is not supported by file_fdw because it's for COPY TO.
  	 */
--- 69,75 
  	{null, ForeignTableRelationId},
  	{encoding, ForeignTableRelationId},
  	{force_not_null, AttributeRelationId},
! 	{force_null, AttributeRelationId},
  	/*
  	 * force_quote is not supported by file_fdw because it's for COPY TO.
  	 */
*** file_fdw_validator(PG_FUNCTION_ARGS)
*** 187,192 
--- 187,193 
  	Oid			catalog = PG_GETARG_OID(1);
  	char	   *filename = NULL;
  	DefElem*force_not_null = NULL;
+ 	DefElem*force_null = NULL;
  	List	   *other_options = NIL;
  	ListCell   *cell;
  
*** file_fdw_validator(PG_FUNCTION_ARGS)
*** 243,252 
  		}
  
  		/*
! 		 * Separate out filename and force_not_null, since ProcessCopyOptions
! 		 * won't accept them.  (force_not_null only comes in a boolean
! 		 * per-column flavor here.)
  		 */
  		if (strcmp(def-defname, filename) == 0)
  		{
  			if (filename)
--- 244,253 
  		}
  
  		/*
! 		 * Separate out filename and column-specific options, since
! 		 * ProcessCopyOptions won't accept them.
  		 */
+ 
  		if (strcmp(def-defname, filename) == 0)
  		{
  			if (filename)
*** file_fdw_validator(PG_FUNCTION_ARGS)
*** 255,270 
  		 errmsg(conflicting or redundant options)));
  			filename = defGetString(def);
  		}
  		else if (strcmp(def-defname, force_not_null) == 0)
  		{
  			if (force_not_null)
  ereport(ERROR,
  		(errcode(ERRCODE_SYNTAX_ERROR),
! 		 errmsg(conflicting or redundant options)));
  			force_not_null = 

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

2014-03-02 Thread Andrew Dunstan


On 03/02/2014 10:06 PM, Ian Lawrence Barwick wrote:

2014-03-02 8:26 GMT+09:00 Andrew Dunstan and...@dunslane.net:

On 01/29/2014 10:59 AM, Ian Lawrence Barwick wrote:

2014/1/29 Ian Lawrence Barwick barw...@gmail.com:

2014-01-29 Andrew Dunstan and...@dunslane.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



--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


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

2014-03-01 Thread Andrew Dunstan


On 01/29/2014 10:59 AM, Ian Lawrence Barwick wrote:

2014/1/29 Ian Lawrence Barwick barw...@gmail.com:

2014-01-29 Andrew Dunstan and...@dunslane.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



diff --git a/contrib/file_fdw/data/text.csv b/contrib/file_fdw/data/text.csv
index ed348a9..f55d9cf 100644
--- a/contrib/file_fdw/data/text.csv
+++ b/contrib/file_fdw/data/text.csv
@@ -1,4 +1,5 @@
-AAA,aaa
-XYZ,xyz
-NULL,NULL
-ABC,abc
+AAA,aaa,123,
+XYZ,xyz,,321
+NULL,NULL,NULL,NULL
+NULL,NULL,NULL,NULL
+ABC,abc,,
diff --git a/contrib/file_fdw/file_fdw.c b/contrib/file_fdw/file_fdw.c
index 5639f4d..7fb1dbc 100644
--- a/contrib/file_fdw/file_fdw.c
+++ b/contrib/file_fdw/file_fdw.c
@@ -48,9 +48,9 @@ struct FileFdwOption
 
 /*
  * Valid options for file_fdw.
- * These options are based on the options for COPY FROM command.
- * But note that force_not_null is handled as a boolean option attached to
- * each column, not as a table option.
+ * These options are based on the options for the COPY FROM command.
+ * But note that force_not_null and force_null are handled as boolean options
+ * attached to a column, not as table options.
  *
  * Note: If you are adding new option for user mapping, you need to modify
  * fileGetOptions(), which currently doesn't bother to look at user mappings.
@@ -69,7 +69,7 @@ static const struct FileFdwOption valid_options[] = {
 	{null, ForeignTableRelationId},
 	{encoding, ForeignTableRelationId},
 	{force_not_null, AttributeRelationId},
-
+	{force_null, AttributeRelationId},
 	/*
 	 * force_quote is not supported by file_fdw because it's for COPY TO.
 	 */
@@ -187,6 +187,7 @@ file_fdw_validator(PG_FUNCTION_ARGS)
 	Oid			catalog = PG_GETARG_OID(1);
 	char	   *filename = NULL;
 	DefElem*force_not_null = NULL;
+	DefElem*force_null = NULL;
 	List	   *other_options = NIL;
 	ListCell   *cell;
 
@@ -243,10 +244,10 @@ file_fdw_validator(PG_FUNCTION_ARGS)
 		}
 
 		/*
-		 * Separate out filename and force_not_null, since ProcessCopyOptions
-		 * won't accept them.  (force_not_null only comes in a boolean
-		 * per-column flavor here.)
+		 * Separate out filename and column-specific options, since
+		 * ProcessCopyOptions won't accept them.
 		 */
+
 		if (strcmp(def-defname, filename) == 0)
 		{
 			if (filename)
@@ -255,16 +256,42 @@ file_fdw_validator(PG_FUNCTION_ARGS)
 		 errmsg(conflicting or redundant options)));
 			filename = defGetString(def);
 		}
+		/*
+		 * force_not_null is a boolean option; after validation we can discard
+		 * it - it will be retrieved later in get_file_fdw_attribute_options()
+		 */
 		else if (strcmp(def-defname, force_not_null) == 0)
 		{
 			if (force_not_null)
 ereport(ERROR,
 		(errcode(ERRCODE_SYNTAX_ERROR),
-		 errmsg(conflicting or redundant options)));
+		 errmsg(conflicting or redundant options),
+		 errhint(option \force_not_null\ supplied more than once for a column)));
+			if(force_null)
+ereport(ERROR,
+		(errcode(ERRCODE_SYNTAX_ERROR),
+		 errmsg(conflicting or redundant options),
+		 errhint(option \force_not_null\ cannot be used together with \force_null\)));
 			force_not_null = def;
 			/* Don't care what the value is, as long as it's a legal boolean */
 			(void) defGetBoolean(def);
 		}
+		/* See comments for force_not_null above */
+		else if (strcmp(def-defname, force_null) == 0)
+		{
+			if (force_null)
+ereport(ERROR,
+		(errcode(ERRCODE_SYNTAX_ERROR),
+		 errmsg(conflicting or redundant options),
+		 errhint(option \force_null\ supplied more than once for a column)));
+			if(force_not_null)
+ereport(ERROR,
+		(errcode(ERRCODE_SYNTAX_ERROR),
+		 errmsg(conflicting or redundant options),
+		 errhint(option \force_null\ cannot be used together with 

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

2014-01-29 Thread Ian Lawrence Barwick
2014/1/29 Ian Lawrence Barwick barw...@gmail.com:
 2014-01-29 Andrew Dunstan and...@dunslane.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
diff --git a/contrib/file_fdw/data/text.csv b/contrib/file_fdw/data/text.csv
new file mode 100644
index ed348a9..c7e243c
*** a/contrib/file_fdw/data/text.csv
--- b/contrib/file_fdw/data/text.csv
***
*** 1,4 
! AAA,aaa
! XYZ,xyz
! NULL,NULL
! ABC,abc
--- 1,4 
! AAA,aaa,123,
! XYZ,xyz,,321
! NULL,NULL,NULL,NULL
! ABC,abc,,
diff --git a/contrib/file_fdw/file_fdw.c b/contrib/file_fdw/file_fdw.c
new file mode 100644
index 5639f4d..5877512
*** a/contrib/file_fdw/file_fdw.c
--- b/contrib/file_fdw/file_fdw.c
*** struct FileFdwOption
*** 48,56 
  
  /*
   * Valid options for file_fdw.
!  * These options are based on the options for COPY FROM command.
!  * But note that force_not_null is handled as a boolean option attached to
!  * each column, not as a table option.
   *
   * Note: If you are adding new option for user mapping, you need to modify
   * fileGetOptions(), which currently doesn't bother to look at user mappings.
--- 48,56 
  
  /*
   * Valid options for file_fdw.
!  * These options are based on the options for the COPY FROM command.
!  * But note that force_not_null and force_null are handled as boolean options
!  * attached to a column, not as a table option.
   *
   * Note: If you are adding new option for user mapping, you need to modify
   * fileGetOptions(), which currently doesn't bother to look at user mappings.
*** static const struct FileFdwOption valid_
*** 69,75 
  	{null, ForeignTableRelationId},
  	{encoding, ForeignTableRelationId},
  	{force_not_null, AttributeRelationId},
! 
  	/*
  	 * force_quote is not supported by file_fdw because it's for COPY TO.
  	 */
--- 69,75 
  	{null, ForeignTableRelationId},
  	{encoding, ForeignTableRelationId},
  	{force_not_null, AttributeRelationId},
! 	{force_null, AttributeRelationId},
  	/*
  	 * force_quote is not supported by file_fdw because it's for COPY TO.
  	 */
*** file_fdw_validator(PG_FUNCTION_ARGS)
*** 187,192 
--- 187,193 
  	Oid			catalog = PG_GETARG_OID(1);
  	char	   *filename = NULL;
  	DefElem*force_not_null = NULL;
+ 	DefElem*force_null = NULL;
  	List	   *other_options = NIL;
  	ListCell   *cell;
  
*** file_fdw_validator(PG_FUNCTION_ARGS)
*** 243,252 
  		}
  
  		/*
! 		 * Separate out filename and force_not_null, since ProcessCopyOptions
! 		 * won't accept them.  (force_not_null only comes in a boolean
! 		 * per-column flavor here.)
  		 */
  		if (strcmp(def-defname, filename) == 0)
  		{
  			if (filename)
--- 244,253 
  		}
  
  		/*
! 		 * Separate out filename and column-specific options, since
! 		 * ProcessCopyOptions won't accept them.
  		 */
+ 
  		if (strcmp(def-defname, filename) == 0)
  		{
  			if (filename)
*** file_fdw_validator(PG_FUNCTION_ARGS)
*** 255,270 
  		 errmsg(conflicting or redundant options)));
  			filename = defGetString(def);
  		}
  		else if (strcmp(def-defname, force_not_null) == 0)
  		{
  			if (force_not_null)
  ereport(ERROR,
  		(errcode(ERRCODE_SYNTAX_ERROR),
! 		 errmsg(conflicting or redundant options)));
  			force_not_null = def;
  			/* Don't care what the value is, as long as it's a legal boolean */
  			(void) defGetBoolean(def);
  		}
  		else
  			other_options = lappend(other_options, def);
  	}
--- 256,297 
  		 errmsg(conflicting or redundant options)));
  			filename = defGetString(def);
  		}
+ 		/*
+ 		 * force_not_null is a boolean option; after validation we can discard
+ 		 * it - it will be retrieved later in get_file_fdw_attribute_options()
+ 		 */
  		else if (strcmp(def-defname, force_not_null) == 0)
  		{
  			if (force_not_null)
  ereport(ERROR,
  		(errcode(ERRCODE_SYNTAX_ERROR),
! 		 errmsg(conflicting or redundant options),
! 		 errhint(option \force_not_null\ supplied more than once for a column)));
! 			if(force_null)
! ereport(ERROR,
! 		(errcode(ERRCODE_SYNTAX_ERROR),
! 		 errmsg(conflicting or redundant options),
! 		 errhint(option \force_not_null\ cannot be used together with \force_null\)));
  			force_not_null = def;
  			/* Don't care what the value is, as long as it's a legal boolean */
  			(void) defGetBoolean(def);
  		}
+ 		/* See comments for force_not_null above */

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

2014-01-28 Thread Ian Lawrence Barwick
2013-11-01 Payal Singh pa...@omniti.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=g...@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 

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

2014-01-28 Thread Andrew Dunstan


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




--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


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

2014-01-28 Thread Ian Lawrence Barwick
2014-01-29 Andrew Dunstan and...@dunslane.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


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


[HACKERS] Review: Patch FORCE_NULL option for copy COPY in CSV mode

2013-10-31 Thread Payal Singh
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=g...@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