Postgres Pro
Downloads
Home   >   mailing lists

Thread: Handling NULL dates in "copy from" statement

Handling NULL dates in "copy from" statement

From
"Tim Nelson"
Date:
Is there a way to handle NULL date fields
that I am trying to import from another database?

ex:

...for a table with a single date field that allows NULL...

echo "12/31/2000" | psql -c "copy date_tab from stdin using delimiters '|' "
db_name

...works, but if I use a blank date...

echo "" | psql -c "copy date_tab from stdin using delimiters '|' " db_name

...it fails with "Bad date external representation"...

I've tried replacing the empty string with "NULL" and adding " with null as
'' " to no avail.

Any ideas?  Thanks.  Tim

Re: Handling NULL dates in "copy from" statement

From
Jeff Eckermann
Date:
The "with null as ''" should work: it does for me.
Are you sure that the field truly is empty, i.e. there
is no nonprintable character lurking in there?

--- Tim Nelson <nelsonx@earthlink.com> wrote:
> Is there a way to handle NULL date fields
> that I am trying to import from another database?
>
> ex:
>
> ...for a table with a single date field that allows
> NULL...
>
> echo "12/31/2000" | psql -c "copy date_tab from
> stdin using delimiters '|' "
> db_name
>
> ...works, but if I use a blank date...
>
> echo "" | psql -c "copy date_tab from stdin using
> delimiters '|' " db_name
>
> ...it fails with "Bad date external
> representation"...
>
> I've tried replacing the empty string with "NULL"
> and adding " with null as
> '' " to no avail.
>
> Any ideas?  Thanks.  Tim
>
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 2: you can get off all lists at once with the
> unregister command
>     (send "unregister YourEmailAddressHere" to
majordomo@postgresql.org)


__________________________________________________
Do You Yahoo!?
Send your FREE holiday greetings online!
http://greetings.yahoo.com

Re: Handling NULL dates in "copy from" statement

From
Jason Earl
Date:
Try replacing the empty string with \N, that's what copy reads as
NULL.

Jason

"Tim Nelson" <nelsonx@earthlink.com> writes:

> Is there a way to handle NULL date fields
> that I am trying to import from another database?
>
> ex:
>
> ...for a table with a single date field that allows NULL...
>
> echo "12/31/2000" | psql -c "copy date_tab from stdin using delimiters '|' "
> db_name
>
> ...works, but if I use a blank date...
>
> echo "" | psql -c "copy date_tab from stdin using delimiters '|' " db_name
>
> ...it fails with "Bad date external representation"...
>
> I've tried replacing the empty string with "NULL" and adding " with null as
> '' " to no avail.
>
> Any ideas?  Thanks.  Tim
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)