Обсуждение: Inserting a Null date.

I am trying to add entries to my database via a Perl script, where some
fields are optional, that is, nulls are allowed. When inserting an
entry, I include every field in the insert statement, but leave their
value undefined if it is optional and left blank. This works for every
field but date fields. Setting a date field to undefined generates the
error "Bad date external representation". Is there a way to leave a date
field null while still having it as an argument in an insert (i.e. INSET
INTO table (id, mydate) VALUES (1, NULL) ), or will I need to make
several different insertion statements for each different case?

Thank you,
Rob Mosher

Rob,

> I am trying to add entries to my database via a Perl script, where some
> fields are optional, that is, nulls are allowed. When inserting an
> entry, I include every field in the insert statement, but leave their
> value undefined if it is optional and left blank. This works for every
> field but date fields. Setting a date field to undefined generates the
> error "Bad date external representation". Is there a way to leave a date
> field null while still having it as an argument in an insert (i.e. INSET
> INTO table (id, mydate) VALUES (1, NULL) ), or will I need to make
> several different insertion statements for each different case?

Inserting a null should work fine.   Are you sure Perl isn't passing an empty
string ( '' ) instead?

--
-Josh Berkus
 Aglio Database Solutions
 San Francisco

Rob,

I checked on your problem; DBD::Pg correctly interprets undef as NULL, and
PostgreSQL will accept a NULL in any NULLable date column.   So there's
something else going on  in your program.   Or you're using the wrong/really
old DBI driver.

--
-Josh Berkus
 Aglio Database Solutions
 San Francisco

Rob Mosher wrote:

> error "Bad date external representation". Is there a way to leave a date
> field null while still having it as an argument in an insert (i.e. INSET
> INTO table (id, mydate) VALUES (1, NULL) ), or will I need to make
> several different insertion statements for each different case?

Works like a charm for me. What version are you using?

I'm using:
root@plasma:~# psql --version
psql (PostgreSQL) 7.3.4

plasma=# CREATE TEMP TABLE demo (id_demo int, datum date);
CREATE TABLE
plasma=# INSERT INTO demo (id_demo, datum) values (1, NULL);
INSERT 10244653 1

HTH
--
  e-Trolley Sayegh & John, Nabil Sayegh
  Tel.: 0700 etrolley /// 0700 38765539
  Fax.: +49 69 8299381-8
  PGP : http://www.e-trolley.de

Rob,

> That may be the problem. I'm using debian-sparc, and I recently had a
> problem were it kicked me back to an older version of pgperl. I may have
> to investigate this on the debian mailing list.

The problem is our last release coincided poorly with the Potato (or was it
Woody?) release of Debian.   As a result, the PostgreSQL offered through
Debian Stable is 7.2.1 ... our version from about 17 months ago.   Also,
ironically, it's not teriffically "stable" and needs some patches.

If you can light a fire under the Debian packaging people and get them to
update, we'd all be grateful.

--
-Josh Berkus
 Aglio Database Solutions
 San Francisco

Josh,

That may be the problem. I'm using debian-sparc, and I recently had a
problem were it kicked me back to an older version of pgperl. I may have
to investigate this on the debian mailing list.

Thanks,
Rob

Josh Berkus wrote:

>Rob,
>
>I checked on your problem; DBD::Pg correctly interprets undef as NULL, and
>PostgreSQL will accept a NULL in any NULLable date column.   So there's
>something else going on  in your program.   Or you're using the wrong/really
>old DBI driver.
>
>
>