Re: primary key error with PostgreSQL

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

On 5/20/05, Brett Sanger <swiftone@xxxxxxxx.xxx> wrote:
> On Fri, May 20, 2005 at 07:25:35AM +0100, Tony Bowden wrote:
> > On Fri, May 20, 2005 at 07:03:23AM +0100, cesmky@xxxxx.xxx wrote:
> > >   itemid INTEGER PRIMARY KEY,
> >
> > On SQLite this acts as an "auto incrementing" column. So when you don't
> > give a value for it, SQLite will fill one in for you. This isn't true o=
f
> > Pg. You either need to give it a value, or add a sequence on that
> > column if you want an automagic value.
>=20
> The "easiest" way to use a sequence in this case would be to have:
>=20
> itemid SERIAL PRIMARY KEY
>=20
> which would be similar to the behavior SQLite gives you.  If you look up
> "serial" and "sequence" in the postgres docs you should fine much more
> info.
>=20

I dropped the table (no foreign keys) and recreated it with this:

meal=3D> CREATE TABLE item (
meal(>     itemid SERIAL PRIMARY KEY,
meal(>     description TEXT,
meal(>     qty INTEGER  =20
meal(> );
NOTICE:  CREATE TABLE will create implicit sequence "item_itemid_seq"
for "serial" column "item.itemid"
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index
"item_pkey" for table "item"
CREATE TABLE
meal=3D> \q

I still get the exact same error message :-(. I notice the SQL in the
error message includes itemid in the field list for the INSERT. Does
Class::DBI actually ask the database what the primary key is or does
it just use the first element in the fields list? Either way it should
work, right?

Can't insert new XmasMeal::Item: DBD::Pg::st execute failed: ERROR:=20
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