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