Re: [INTERFACES] copy command & null datetime

At 12:49 AM 3/8/99 -0500, you wrote:
>On Sun, 7 Mar 1999, Ken J. Wright wrote:
>
>> The following example will cause COPY to fail on input:
>>
>> xxx|yyy|zzz||aaa|bbb|
>>
>> where the empty field in between zzz & aaa is of type datetime (don't know
>> about other types). I know a \N will fix this, but interpreting an empty
>> import field as NULL would certainly beef up COPY as other database systems
>> don't output the \N which is unique to PostgreSQL. Wish list item?
>
>Yes, I discovered this little issue the other day.  What is this \N
>option, though?  I have some 900000+ records out of a 2.1 million row
>table that have NULL date fields.  I ended up sticking in a dummy date (of
>like 1901) for those, because I knew that valid dates stopped at a
>particular lower cutoff date of around 1968, then ignore the earlier date
>altogether.
>

The \N is AFAICT undocumented, as are the other issues involving escaping
with the '\' character in PostgreSQL i/o. This was discussed here last
summer, so some facts snuck out. The COPY command will use the \N on output
as a place holder for a null field when required. Also, it will read \N as
a null on input. My example above will succeed with;
xxx|yyy|zzz|\N|aaa|bbb| (case is important!). If you're creating an export
program, no big deal eh? But I moved a few tables from Informix to pg.
Informix very nicely outputs '|' separated unload files. But I had to edit
the files and search/replace all || with |\N|. Not terrible on small files,
one time. But I'd rather not at all ;-) Other escaping combinations can
clobber your data as well. I seem to recall that this was a front end
issue, and that the backend did no escaping.

Ken