Re: [HACKERS] Cannot dump/restore text value \N - Mailing list pgsql-patches

On Wed, 08 Oct 2003 11:31:30 +0200, I wrote:
>There haven't been too much changes in this area between 7.3 and 7.4.

Here is the patch for 7.3.4 ...

Bruce, I noticed that the original patch submission didn't contain
anything useful as a cvs log message:

Make COPY FROM a bit more compatible with COPY TO regarding
backslashes, especially \N.

Servus
 Manfred
diff -ruN ../base/src/backend/commands/copy.c src/backend/commands/copy.c
--- ../base/src/backend/commands/copy.c    2003-04-26 00:14:33.000000000 +0200
+++ src/backend/commands/copy.c    2003-10-08 16:30:14.000000000 +0200
@@ -62,7 +62,8 @@
          FILE *fp, char *delim, char *null_print);
 static Oid    GetInputFunction(Oid type);
 static Oid    GetTypeElement(Oid type);
-static char *CopyReadAttribute(FILE *fp, const char *delim, CopyReadResult *result);
+static char *CopyReadAttribute(FILE *fp, const char *delim, const char *nullst,
+                               CopyReadResult *result, bool *isnull);
 static void CopyAttributeOut(FILE *fp, char *string, char *delim);
 static List *CopyGetAttnums(Relation rel, List *attnamelist);

@@ -931,6 +932,7 @@
     {
         bool        skip_tuple;
         Oid            loaded_oid = InvalidOid;
+        bool        isnull;

         CHECK_FOR_INTERRUPTS();

@@ -954,7 +956,7 @@

             if (file_has_oids)
             {
-                string = CopyReadAttribute(fp, delim, &result);
+                string = CopyReadAttribute(fp, delim, null_print, &result, &isnull);

                 if (result == END_OF_FILE && *string == '\0')
                 {
@@ -963,7 +965,7 @@
                     break;
                 }

-                if (strcmp(string, null_print) == 0)
+                if (isnull)
                     elog(ERROR, "NULL Oid");
                 else
                 {
@@ -990,7 +992,7 @@
                     elog(ERROR, "Missing data for column \"%s\"",
                          NameStr(attr[m]->attname));

-                string = CopyReadAttribute(fp, delim, &result);
+                string = CopyReadAttribute(fp, delim, null_print, &result, &isnull);

                 if (result == END_OF_FILE && *string == '\0' &&
                     cur == attnumlist && !file_has_oids)
@@ -1000,7 +1002,7 @@
                     break;        /* out of per-attr loop */
                 }

-                if (strcmp(string, null_print) == 0)
+                if (isnull)
                 {
                     /* we read an SQL NULL, no need to do anything */
                 }
@@ -1029,7 +1031,7 @@
             {
                 if (attnumlist == NIL && !file_has_oids)
                 {
-                    string = CopyReadAttribute(fp, delim, &result);
+                    string = CopyReadAttribute(fp, delim, null_print, &result, &isnull);
                     if (result == NORMAL_ATTR || *string != '\0')
                         elog(ERROR, "Extra data after last expected column");
                     if (result == END_OF_FILE)
@@ -1158,8 +1160,6 @@
          */
         for (i = 0; i < num_defaults; i++)
         {
-            bool        isnull;
-
             values[defmap[i]] = ExecEvalExpr(defexprs[i], econtext,
                                              &isnull, NULL);
             if (!isnull)
@@ -1175,7 +1175,6 @@
             {
                 Node       *node = constraintexprs[i];
                 Const       *con;
-                bool        isnull;

                 if (node == NULL)
                     continue;    /* no constraint for this attr */
@@ -1316,15 +1315,14 @@
  *        END_OF_FILE:    EOF indication
  * In all cases, the string read up to the terminator is returned.
  *
- * Note: This function does not care about SQL NULL values -- it
- * is the caller's responsibility to check if the returned string
- * matches what the user specified for the SQL NULL value.
- *
  * delim is the column delimiter string.
+ * nullst says how NULL values are represented.
+ * *isnull is set true if a null attribute, else false.
  */

 static char *
-CopyReadAttribute(FILE *fp, const char *delim, CopyReadResult *result)
+CopyReadAttribute(FILE *fp, const char *delim, const char *nullst,
+                               CopyReadResult *result, bool *isnull)
 {
     int            c;
     int            delimc = (unsigned char) delim[0];
@@ -1332,6 +1330,17 @@
     unsigned char s[2];
     char       *cvt;
     int            j;
+    bool        matchnull = true;
+    int            matchlen = 0;
+#define CHECK_MATCH(c) \
+    do { \
+        if (matchnull) \
+            if (c == nullst[matchlen]) \
+                ++matchlen; \
+            else \
+                matchnull = false; \
+    } while (0)
+

     s[1] = 0;

@@ -1357,6 +1366,7 @@
         }
         if (c == delimc)
             break;
+        CHECK_MATCH(c);
         if (c == '\\')
         {
             c = CopyGetChar(fp);
@@ -1365,6 +1375,7 @@
                 *result = END_OF_FILE;
                 goto copy_eof;
             }
+            CHECK_MATCH(c);
             switch (c)
             {
                 case '0':
@@ -1384,11 +1395,13 @@
                         {
                             val = (val << 3) + OCTVALUE(c);
                             CopyDonePeek(fp, c, true /* pick up */ );
+                            CHECK_MATCH(c);
                             c = CopyPeekChar(fp);
                             if (ISOCTAL(c))
                             {
                                 val = (val << 3) + OCTVALUE(c);
                                 CopyDonePeek(fp, c, true /* pick up */ );
+                                CHECK_MATCH(c);
                             }
                             else
                             {
@@ -1413,15 +1426,6 @@
                     }
                     break;

-                    /*
-                     * This is a special hack to parse `\N' as
-                     * <backslash-N> rather then just 'N' to provide
-                     * compatibility with the default NULL output. -- pe
-                     */
-                case 'N':
-                    appendStringInfoCharMacro(&attribute_buf, '\\');
-                    c = 'N';
-                    break;
                 case 'b':
                     c = '\b';
                     break;
@@ -1464,6 +1468,7 @@
                     *result = END_OF_FILE;
                     goto copy_eof;
                 }
+                CHECK_MATCH(c);
                 appendStringInfoCharMacro(&attribute_buf, c);
             }
         }
@@ -1471,6 +1476,8 @@

 copy_eof:

+    *isnull = (matchnull && (nullst[matchlen] == '\0'));
+
     if (client_encoding != server_encoding)
     {
         cvt = (char *) pg_client_to_server((unsigned char *) attribute_buf.data,
@@ -1486,6 +1493,7 @@
     }

     return attribute_buf.data;
+#undef CHECK_MATCH
 }

 static void
diff -ruN ../base/src/test/regress/expected/copy2.out src/test/regress/expected/copy2.out
--- ../base/src/test/regress/expected/copy2.out    2002-10-19 03:35:43.000000000 +0200
+++ src/test/regress/expected/copy2.out    2003-10-07 21:49:08.000000000 +0200
@@ -2,7 +2,7 @@
     a serial,
     b int,
     c text not null default 'stuff',
-    d text not null,
+    d text,
     e text
 );
 NOTICE:  CREATE TABLE will create implicit sequence 'x_a_seq' for SERIAL column 'x.a'
@@ -49,23 +49,38 @@
 SET autocommit TO 'on';
 -- various COPY options: delimiters, oids, NULL string
 COPY x (b, c, d, e) from stdin with oids delimiter ',' null 'x';
+COPY x from stdin WITH DELIMITER AS ';' NULL AS '';
+COPY x from stdin WITH DELIMITER AS ':' NULL AS '\\X';
 -- check results of copy in
 SELECT * FROM x;
-   a   | b  |   c   |   d    |          e
--------+----+-------+--------+----------------------
- 10000 | 21 | 31    | 41     | before trigger fired
- 10001 | 22 | 32    | 42     | before trigger fired
- 10002 | 23 | 33    | 43     | before trigger fired
- 10003 | 24 | 34    | 44     | before trigger fired
- 10004 | 25 | 35    | 45     | before trigger fired
- 10005 | 26 | 36    | 46     | before trigger fired
-     6 |    | 45    | 80     | before trigger fired
-     1 |  1 | stuff | test_1 | after trigger fired
-     2 |  2 | stuff | test_2 | after trigger fired
-     3 |  3 | stuff | test_3 | after trigger fired
-     4 |  4 | stuff | test_4 | after trigger fired
-     5 |  5 | stuff | test_5 | after trigger fired
-(12 rows)
+   a   | b  |     c      |   d    |          e
+-------+----+------------+--------+----------------------
+  9999 |    | \N         | NN     | before trigger fired
+ 10000 | 21 | 31         | 41     | before trigger fired
+ 10001 | 22 | 32         | 42     | before trigger fired
+ 10002 | 23 | 33         | 43     | before trigger fired
+ 10003 | 24 | 34         | 44     | before trigger fired
+ 10004 | 25 | 35         | 45     | before trigger fired
+ 10005 | 26 | 36         | 46     | before trigger fired
+     6 |    | 45         | 80     | before trigger fired
+     7 |    | x          | \x     | before trigger fired
+     8 |    | ,          | \,     | before trigger fired
+  3000 |    | c          |        | before trigger fired
+  4000 |    | C          |        | before trigger fired
+  4001 |  1 | empty      |        | before trigger fired
+  4002 |  2 | null       |        | before trigger fired
+  4003 |  3 | Backslash  | \      | before trigger fired
+  4004 |  4 | BackslashX | \X     | before trigger fired
+  4005 |  5 | N          | N      | before trigger fired
+  4006 |  6 | BackslashN | \N     | before trigger fired
+  4007 |  7 | XX         | XX     | before trigger fired
+  4008 |  8 | Delimiter  | :      | before trigger fired
+     1 |  1 | stuff      | test_1 | after trigger fired
+     2 |  2 | stuff      | test_2 | after trigger fired
+     3 |  3 | stuff      | test_3 | after trigger fired
+     4 |  4 | stuff      | test_4 | after trigger fired
+     5 |  5 | stuff      | test_5 | after trigger fired
+(25 rows)

 -- COPY w/ oids on a table w/o oids should fail
 CREATE TABLE no_oids (
@@ -81,6 +96,7 @@
 ERROR:  COPY: table "no_oids" does not have OIDs
 -- check copy out
 COPY x TO stdout;
+9999    \N    \\N    NN    before trigger fired
 10000    21    31    41    before trigger fired
 10001    22    32    42    before trigger fired
 10002    23    33    43    before trigger fired
@@ -88,12 +104,25 @@
 10004    25    35    45    before trigger fired
 10005    26    36    46    before trigger fired
 6    \N    45    80    before trigger fired
+7    \N    x    \\x    before trigger fired
+8    \N    ,    \\,    before trigger fired
+3000    \N    c    \N    before trigger fired
+4000    \N    C    \N    before trigger fired
+4001    1    empty        before trigger fired
+4002    2    null    \N    before trigger fired
+4003    3    Backslash    \\    before trigger fired
+4004    4    BackslashX    \\X    before trigger fired
+4005    5    N    N    before trigger fired
+4006    6    BackslashN    \\N    before trigger fired
+4007    7    XX    XX    before trigger fired
+4008    8    Delimiter    :    before trigger fired
 1    1    stuff    test_1    after trigger fired
 2    2    stuff    test_2    after trigger fired
 3    3    stuff    test_3    after trigger fired
 4    4    stuff    test_4    after trigger fired
 5    5    stuff    test_5    after trigger fired
 COPY x (c, e) TO stdout;
+\\N    before trigger fired
 31    before trigger fired
 32    before trigger fired
 33    before trigger fired
@@ -101,12 +130,25 @@
 35    before trigger fired
 36    before trigger fired
 45    before trigger fired
+x    before trigger fired
+,    before trigger fired
+c    before trigger fired
+C    before trigger fired
+empty    before trigger fired
+null    before trigger fired
+Backslash    before trigger fired
+BackslashX    before trigger fired
+N    before trigger fired
+BackslashN    before trigger fired
+XX    before trigger fired
+Delimiter    before trigger fired
 stuff    after trigger fired
 stuff    after trigger fired
 stuff    after trigger fired
 stuff    after trigger fired
 stuff    after trigger fired
 COPY x (b, e) TO stdout WITH NULL 'I''m null';
+I'm null    before trigger fired
 21    before trigger fired
 22    before trigger fired
 23    before trigger fired
@@ -114,6 +156,18 @@
 25    before trigger fired
 26    before trigger fired
 I'm null    before trigger fired
+I'm null    before trigger fired
+I'm null    before trigger fired
+I'm null    before trigger fired
+I'm null    before trigger fired
+1    before trigger fired
+2    before trigger fired
+3    before trigger fired
+4    before trigger fired
+5    before trigger fired
+6    before trigger fired
+7    before trigger fired
+8    before trigger fired
 1    after trigger fired
 2    after trigger fired
 3    after trigger fired
diff -ruN ../base/src/test/regress/sql/copy2.sql src/test/regress/sql/copy2.sql
--- ../base/src/test/regress/sql/copy2.sql    2002-10-19 03:35:43.000000000 +0200
+++ src/test/regress/sql/copy2.sql    2003-10-07 21:49:08.000000000 +0200
@@ -2,7 +2,7 @@
     a serial,
     b int,
     c text not null default 'stuff',
-    d text not null,
+    d text,
     e text
 );

@@ -27,6 +27,7 @@
 FOR EACH ROW EXECUTE PROCEDURE fn_x_before();

 COPY x (a, b, c, d, e) from stdin;
+9999    \N    \\N    \NN    \N
 10000    21    31    41    51
 \.

@@ -75,6 +76,24 @@
 -- various COPY options: delimiters, oids, NULL string
 COPY x (b, c, d, e) from stdin with oids delimiter ',' null 'x';
 500000,x,45,80,90
+500001,x,\x,\\x,\\\x
+500002,x,\,,\\\,,\\
+\.
+
+COPY x from stdin WITH DELIMITER AS ';' NULL AS '';
+3000;;c;;
+\.
+
+COPY x from stdin WITH DELIMITER AS ':' NULL AS '\\X';
+4000:\X:C:\X:\X
+4001:1:empty::
+4002:2:null:\X:\X
+4003:3:Backslash:\\:\\
+4004:4:BackslashX:\\X:\\X
+4005:5:N:\N:\N
+4006:6:BackslashN:\\N:\\N
+4007:7:XX:\XX:\XX
+4008:8:Delimiter:\::\:
 \.

 -- check results of copy in