null dates and '?' under pgdbi.

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+(at)pitt(dot)edu
University of Pittsburgh Medical Center Phone: +1 412 681-3482
Western Psychiatric Institute and Clinic
Program in Epidemiology