Thread: 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. > > >