Received: from post-ofc04.srv.cis.pitt.edu (post-ofc04.srv.cis.pitt.edu
	[136.142.185.11]) by hub.org (8.9.1/8.9.1) with ESMTP id LAA14123
	for <pgsql-interfaces@postgresql.org>;
	Mon, 9 Nov 1998 11:43:13 -0500 (EST) (envelope-from tml+@pitt.edu)
Received: from localhost (root@localhost)
	by post-ofc04.srv.cis.pitt.edu (8.8.8/8.8.8/cispo-7.2.2.2)
	ID <LAA16286@post-ofc04.srv.cis.pitt.edu> for
	pgsql-interfaces@postgresql.org;
	Mon, 9 Nov 1998 11:43:09 -0500 (EST)
Received: via switchmail; Mon,  9 Nov 1998 11:43:07 -0500 (EST)
Received: from unixs-eval.cis.pitt.edu via qmail
	ID </afs/pitt.edu/service/mailqs/q4/QF.UqFlhQK8XfYH0FzE40>;
	Mon,  9 Nov 1998 11:42:05 -0500 (EST)
Received: from local (tml@localhost)
	by unixs-eval.cis.pitt.edu (8.8.8/client-2.4)
	ID <LAA04600@unixs-eval.cis.pitt.edu>;
	Mon, 9 Nov 1998 11:42:02 -0500 (EST)
Date: Mon, 9 Nov 1998 11:42:02 -0500 (EST)
Message-Id: <199811091642.LAA04600@unixs-eval.cis.pitt.edu>
From: Tom Link <tml+mlists.pgsql.interfaces@imap.pitt.edu>
To: pgsql-interfaces@postgresql.org
Subject: null dates and '?' under pgdbi.
Sender: Tom Link <tml+@pitt.edu>
Cc: Tom Link <tml+@pitt.edu>

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