null dates and '?' under pgdbi. - Mailing list pgsql-interfaces

Postgresql 6.3.8-2 (from Debian 2.0 distribution)
Perl       5.004_04
Pg.pm      1.6
DBI.pm     1.85

I'm having trouble loading empty dates using perl's dbi::pg and the
method where you prepare an insert/update with '?'s which get loaded
via executes.  I tried "\N" like I saw in the archives and null which
works in psql.

The following sample code only load the first and last case:

<---------------------------------------------------------------->
#! /usr/bin/perl

use DBI;

$dbh = DBI->connect("dbi:Pg:dbname='test'")
  || die "Error connecting to datbase $dbase: $DBI::errstr\n";

$sth = $dbh->do("DROP TABLE foo")|| warn "Error dropping table:
$DBI::errstr\n";
$sth = $dbh->do("

    CREATE TABLE foo (
        id     DECIMAL(5),
        birth  DATE,
        name   VARCHAR(15)
    )

") || die "Error creating new table: $DBI::errstr\n";

##############################################

($sth = $dbh->prepare("

    INSERT INTO foo
      (id, birth, name)
      VALUES(?,?,?)

")) || die "Error preparing insert: $DBI::errstr\n";

while (<DATA>) {
  chomp();
  @data = split (/\s*\|\s*/, $_);
  $sth->execute(@data) || warn "$_: $DBI::errstr\n";
}

##############################################

$sth = $dbh->prepare("select * from foo")
  || die "Error preparing select: $DBI::errstr\n";

$sth->execute() || die "Error preforming select: $DBI::errstr\n";

while (defined ($bar = $sth->fetchrow_hashref)) {
  printf ("%5d | %-15s | %15s | %s\n", $$bar{id}, $$bar{name}, $$bar{birth});
}

__DATA__
201 | 1910-01-21 | Tom
202 |            | Dick
203 | \N         | Harry
204 | null       | John
205 | NULL       | Mary
206 | "\N"       | Chris
207 | '\N'       | Joan
208 | \\N        | Ralph
209 | \\\N       | Bjork
210 | 1929-01-26 | Nancy
<------------------------------------------------------------------>

The output:

<------------------------------------------------------------------>
ERROR:  Bad date external representation
202 |            | Dick: ERROR:  Bad date external representation

ERROR:  Bad date external representation N
203 | \N         | Harry: ERROR:  Bad date external representation N

ERROR:  Bad date external representation null
204 | null       | John: ERROR:  Bad date external representation null

ERROR:  Bad date external representation NULL
205 | NULL       | Mary: ERROR:  Bad date external representation NULL

ERROR:  Bad date external representation "N"
206 | "\N"       | Chris: ERROR:  Bad date external representation "N"

ERROR:  parser: parse error at or near "\"
207 | '\N'       | Joan: ERROR:  parser: parse error at or near "\"

ERROR:  Bad date external representation \N
208 | \\N        | Ralph: ERROR:  Bad date external representation \N

ERROR:  Bad date external representation \N
209 | \\\N       | Bjork: ERROR:  Bad date external representation \N

  201 | Tom             |      01-21-1910 |
  210 | Nancy           |      01-26-1929 |
<------------------------------------------------------------------>

Thanks,
Tom

Tom Link -- N3JNN                          Email: tml+@pitt.edu
University of Pittsburgh Medical Center    Phone: +1 412 681-3482
Western Psychiatric Institute and Clinic
Program in Epidemiology