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

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Rob,

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

--
-Josh Berkus
Aglio Database Solutions
San Francisco


---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

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


---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo (AT) postgresql (DOT) org

Rob Mosher wrote:

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

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

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:

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Rob,

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


---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings