primary key error with PostgreSQL

[prev] [thread] [next] [Date index for 2005/05/20]

I am getting an error message when calling create. I do not get this error when the back-end database is an SQLite database. Only when I use PostgreSQL.
(The example I am trying to run is from http://www.perladvent.org/2002/23rd/.)

The error message I am getting from the call to create is:

Can't insert new XmasMeal::Item: DBD::Pg::st execute failed: ERROR:  null value
in column "itemid" violates not-null constraint [for Statement "INSERT INTO item
(itemid, description, qty)
VALUES (?, ?, ?)
"] at /usr/lib/perl5/vendor_perl/5.8.2/DBIx/ContextualFetch.pm line 51.
 at ./test-1.pl line 3

The SQL used to create the table "item" (which XmasMeal::Item uses) is:

CREATE TABLE item (
  itemid INTEGER PRIMARY KEY,
  description TEXT,
  qty INTEGER);

The create call is as follows:
my $item = XmasMeal::Item->create({ description => "Turkey",
                                    qty => 1 });

I was under the impression that columns() would treat
the first column name as the primary key by default.
It does when I use SQLite (and inherit XmasMeal::DBI
from Class::DBI::SQLite) but not now.

Can anyone help me solve this error?

OS: Linux
PostgreSQL: 7.4.5
Class::DBI: 0.96
Class::DBI::Pg: 0.06
DBI: 1.48
DBD::Pg: 1.22 (1.41 is available according to perl -MCPAN)

Code Below:

My base class for the example is XmasMeal::DBI:

package XmasMeal::DBI;
use base qw(Class::DBI);
use strict;
use warnings;

XmasMeal::DBI->set_db('Main', # always 'Main'
                     'dbi:Pg:dbname=meal', # database handle info
                     'xxxx', # username
                     'yyyy'); # password
1;

My Item class is XmasMeal::Item:

package XmasMeal::Item;
use base qw(XmasMeal::DBI);
use strict;
use warnings;

XmasMeal::Item->table('item');
XmasMeal::Item->columns(All => qw(itemid
                                  description
                                  qty));
1;

Script failing is test-1.pl:

#!/usr/bin/perl
use XmasMeal::Item;
my $item = XmasMeal::Item->create({ description => "Turkey",
                                    qty => 1 });
$item->update;