Re: [HACKERS] Cannot dump/restore text value \N

On Wed, 8 Oct 2003 11:33:24 -0400 (EDT), Bruce Momjian
<pgman(at)candle(dot)pha(dot)pa(dot)us> wrote:
>Can someone explain what was broken?

COPY FROM removed backslashes before comparing the input to the
external null representation. (It had a hard-wired special code path
that allowed \N to be recognized.) The text \N was (and still is)
correctly exported as \\N, but \\N was imported as NULL.

> Was it only for non-standard NULL strings?

There were problems in both cases.
Standard NULL representation:

fred=# CREATE TABLE a (c1 text, c2 text);
CREATE TABLE
fred=# INSERT INTO a VALUES ('\\N', null);
INSERT 577147 1
fred=# SELECT * FROM a;
c1 | c2
----+----
\N |
(1 row)

fred=# COPY a TO stdout;
\\N \N
fred=# COPY a FROM stdin;
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
>> \\N \N
>> \.
fred=# SELECT * FROM a;
c1 | c2
----+----
\N |
|
(2 rows)

User defined NULL string:

fred=# CREATE TABLE a (c1 text, c2 text);
CREATE TABLE
fred=# INSERT INTO a VALUES ('\\X', null);
INSERT 577140 1
fred=# SELECT * FROM a;
c1 | c2
----+----
\X |
(1 row)

fred=# COPY a TO stdout WITH NULL AS '\\X';
\\X \X
fred=# COPY a FROM stdin WITH NULL AS '\\X';
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
>> \\X \X
>> \.
fred=# SELECT * FROM a;
c1 | c2
----+----
\X |
| X
(2 rows)

> Would it silently fail?

It would silently insert wrong data, unless a constraint (NOT NULL)
prevented it.

> I saw your example and it seemed
>strange we had not seen a bug report before.

Because nobody was crazy enough to store \N in his database ...

Tom has already fixed this issue for cvs head. My 7.4 patch wouldn't
apply anyway (built it against Beta 3). You might want to apply the
7.3.4 version, though.

Should I send a new patch with only the regression tests?

Servus
Manfred