Re: Bug in psql COPY command

"Joshua Jackson" <jjackson@vortech.net> writes:
> A text file containing the following (each field seperated by tabs)
> test1    test2    test3\    test4    1234    test5
> to be copied into a table such as:
> create table test(
>     f1    varchar(10),
>     f2    varchar(10),
>     f3    varchar(10),
>     f4    varchar(10),
>     f5    integer,
>     f6    varchar(10)
> );
> will fail with the error:
> ERROR:    copy:    line 1, Bad integer input format 'test5'

This is not a bug.  Backslash is a quoting character as far as COPY
is concerned, so what you have written says that "test3<tab>test4"
is the data for f3 --- and then of course f4 gets "1234" and f5
gets "test5".

You'd need to write \\ in order to put an actual backslash into f3.

> The same result will be achieved if test3 was followed by an ASCII 0x0

Hmm.  That might be a bug --- COPY uses strchr() to decide whether a
character is a field terminator or not, and so a null will always look
like a terminator.  Net effect is that f3 gets "test3", f4 gets an
empty string (since the tab terminates it), f5 gets "test4".  I assume
you actually saw "Bad integer input format 'test4'" in this case?

I find it hard to get very excited about that, however, since none of
Postgres's input/output conversion routines are designed to handle
embedded null characters.  You certainly shouldn't expect to store
an embedded null in a varchar.  You could get it past COPY by writing
either \000 or \<null>, but the type-specific input routine isn't
going to take it.

            regards, tom lane