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
