serial increments on failed insert

From: David Kammer <djkammer(at)u(dot)washington(dot)edu>
To: pgsql-general(at)postgresql(dot)org
Subject: serial increments on failed insert
Date: 2005-01-15 00:48:12
Message-ID: 41E8684C.40402@u.washington.edu
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Ive noticed what seems to be an odd effect in psql 7.3. It works like this:

1> Create a table:
CREATE TABLE foo
(
sval serial,
uval int UNIQUE
);

2> Run 3 inserts, the second of which fails because it fails the
unique constraint:
INSERT INTO foo VALUES (DEFAULT,1);
INSERT INTO foo VALUES (DEFAULT,1); <--- This fails
INSERT INTO foo VALUES (DEFAULT,2);

3> look at the table:
SELECT * FROM foo;
sval | uval
------+------
1 | 1
3 | 2 <--- look here
(2 rows)

Notice that even though the second insert failed, it still incremented
the serial value. This seems counter intuative to the way that serial
should work. Is this truly a bug, or is there a good work around?

Thanks,
Dave

Responses

Browse pgsql-general by date

  From Date Subject
Next Message David Kammer 2005-01-15 00:57:19 serial increments on failed insert
Previous Message Jim C. Nasby 2005-01-15 00:39:25 Re: OID Usage