pg.ddx.io pgsql-interfaces@postgresql.org mailing list archive
help / color / mirror / Atom feedcopy command & null datetime
6+ messages / 3 participants
[nested] [flat]
* copy command & null datetime
@ 1999-03-08 03:07 Ken J. Wright <ken@ori-ind.com>
0 siblings, 2 replies; 6+ messages in thread
From: Ken J. Wright @ 1999-03-08 03:07 UTC (permalink / raw)
To: pgsql-interfaces
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?
Ken
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: [INTERFACES] copy command & null datetime
@ 1999-03-08 05:49 Brett W. McCoy <bmccoy@lan2wan.com>
parent: Ken J. Wright <ken@ori-ind.com>
1 sibling, 0 replies; 6+ messages in thread
From: Brett W. McCoy @ 1999-03-08 05:49 UTC (permalink / raw)
To: Ken J. Wright <ken@ori-ind.com>; +Cc: pgsql-interfaces
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.
Brett W. McCoy
http://www.lan2wan.com/~bmccoy/
-----------------------------------------------------------------------
A baby is God's opinion that the world should go on.
-- Carl Sandburg
-----BEGIN GEEK CODE BLOCK-----
Version: 3.12
GAT dpu s:-- a C++++ UL++++$ P+ L+++ E W++ N+ o K- w--- O@ M@ !V PS+++
PE Y+ PGP- t++ 5- X+ R+@ tv b+++ DI+++ D+ G++ e>++ h+(---) r++ y++++
------END GEEK CODE BLOCK------
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: [INTERFACES] copy command & null datetime
@ 1999-03-08 06:26 Ken J. Wright <ken@ori-ind.com>
0 siblings, 0 replies; 6+ messages in thread
From: Ken J. Wright @ 1999-03-08 06:26 UTC (permalink / raw)
To: Brett W. McCoy <bmccoy@lan2wan.com>; +Cc: pgsql-interfaces
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: [INTERFACES] copy command & null datetime
@ 1999-03-08 07:40 Tom Lane <tgl@sss.pgh.pa.us>
parent: Ken J. Wright <ken@ori-ind.com>
1 sibling, 0 replies; 6+ messages in thread
From: Tom Lane @ 1999-03-08 07:40 UTC (permalink / raw)
To: Ken J. Wright <ken@ori-ind.com>; +Cc: pgsql-interfaces
"Ken J. Wright" <ken@ori-ind.com> writes:
> 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.
You might call that "beefing up COPY" but I'd call it "breaking COPY".
For data types such as text or varchar, there is a considerable
difference between an empty field value and a NULL field. You
propose to make it impossible to tell the difference.
IMHO, COPY's primary responsibility is to serve as a lossless conduit
for saving and restoring databases, not to be a user-friendly or
foreign-database-compatible data import method. It *must* reliably
distinguish empty strings from NULL fields. If there's some other
data format you want to read, you can use a preprocessor to convert
it into COPY format or INSERT statements.
Now I'm certainly not particularly in love with the \N notation,
and if you have a better idea I'm all ears. But failing to
distinguish empty and NULL is not a better idea.
regards, tom lane
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: [INTERFACES] copy command & null datetime
@ 1999-03-08 08:11 Ken J. Wright <ken@ori-ind.com>
0 siblings, 1 reply; 6+ messages in thread
From: Ken J. Wright @ 1999-03-08 08:11 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-interfaces
At 02:40 AM 3/8/99 -0500, you wrote:
>"Ken J. Wright" <ken@ori-ind.com> writes:
>> 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.
>
>You might call that "beefing up COPY" but I'd call it "breaking COPY".
>For data types such as text or varchar, there is a considerable
>difference between an empty field value and a NULL field. You
>propose to make it impossible to tell the difference.
>
>IMHO, COPY's primary responsibility is to serve as a lossless conduit
>for saving and restoring databases, not to be a user-friendly or
>foreign-database-compatible data import method. It *must* reliably
>distinguish empty strings from NULL fields. If there's some other
>data format you want to read, you can use a preprocessor to convert
>it into COPY format or INSERT statements.
>
>Now I'm certainly not particularly in love with the \N notation,
>and if you have a better idea I'm all ears. But failing to
>distinguish empty and NULL is not a better idea.
>
> regards, tom lane
Ok, I'll take a proper beating here ;-). COPY is intended for use with
PostgreSQL and not as an import/export wizard. And I guess the answer here
is that such a tool should be contributed. I did a simple csv one for
myself to move Paradox data. My main point of concern here, was that many
people face the same challenge when moving an existing database to
Postgres. How do I get it there? If you don't do code, you're in trouble.
But in the specific case of a datetime column, wouldn't you think a null
would be reasonable rather than a failed load? After all, a date can't be
empty, only null.
Cheers!
Ken
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: [INTERFACES] copy command & null datetime
@ 1999-03-08 14:58 Tom Lane <tgl@sss.pgh.pa.us>
parent: Ken J. Wright <ken@ori-ind.com>
0 siblings, 0 replies; 6+ messages in thread
From: Tom Lane @ 1999-03-08 14:58 UTC (permalink / raw)
To: Ken J. Wright <ken@ori-ind.com>; +Cc: pgsql-interfaces
"Ken J. Wright" <ken@ori-ind.com> writes:
> But in the specific case of a datetime column, wouldn't you think a null
> would be reasonable rather than a failed load? After all, a date can't be
> empty, only null.
Well ... if you go down that path, you find yourself trying to remember
which datatypes accept an empty string as meaning NULL and which don't.
I don't much like that idea; NULL is a type-independent concept and
ought to have a type-independent representation.
An example where the behavior would be far from obvious is char(n)
(not varchar, but the fixed-length string type). A zero-length input
wouldn't be a valid value for n>0, so it could be taken to mean NULL.
On the other hand, char(n) has historically accepted inputs of length
< n and blank-padded them, so converting empty input to n spaces would
also be expected behavior.
Of course \N has its own problems in this respect: unless you know about
backslash-escaping rules, it's not obvious that \N is not a legal data
value for a string. But we need an escaping mechanism anyway, in order
to deal with newlines and tabs in text strings, so there has to be some
ugliness of this kind.
regards, tom lane
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~1999-03-08 14:58 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1999-03-08 03:07 copy command & null datetime Ken J. Wright <ken@ori-ind.com>
1999-03-08 05:49 ` Brett W. McCoy <bmccoy@lan2wan.com>
1999-03-08 07:40 ` Tom Lane <tgl@sss.pgh.pa.us>
1999-03-08 06:26 Re: [INTERFACES] copy command & null datetime Ken J. Wright <ken@ori-ind.com>
1999-03-08 08:11 Re: [INTERFACES] copy command & null datetime Ken J. Wright <ken@ori-ind.com>
1999-03-08 14:58 ` Tom Lane <tgl@sss.pgh.pa.us>
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