From: Ken J. Wright <ken@ori-ind.com>
To: Brett W. McCoy <bmccoy@lan2wan.com>
Cc: pgsql-interfaces@postgreSQL.org
Subject: Re: [INTERFACES] copy command & null datetime
Date: Sun, 07 Mar 1999 22:26:23 -0800
Message-ID: <3.0.32.19990307222621.008ad8a0@ren.cncware.com> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-interfaces@postgresql.org
Cc: ken@ori-ind.com, bmccoy@lan2wan.com
Subject: Re: [INTERFACES] copy command & null datetime
In-Reply-To: <3.0.32.19990307222621.008ad8a0@ren.cncware.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox