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

Search Discussions

  • Josh Berkus at Feb 2, 2004 at 9:24 pm
    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
  • Josh Berkus at Feb 2, 2004 at 11:35 pm
    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 at Feb 5, 2004 at 2:28 am
    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.

  • Josh Berkus at Feb 5, 2004 at 5:10 am
    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
  • Nabil Sayegh at Feb 3, 2004 at 10:52 pm

    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

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-novice @
categoriespostgresql
postedFeb 2, '04 at 4:16p
activeFeb 5, '04 at 5:10a
posts6
users3
websitepostgresql.org
irc#postgresql

People

Translate

site design / logo © 2022 Grokbase