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
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
