3

I have a simple table in my PostgreSQL database like this:

CREATE TABLE person_type
(
  id serial NOT NULL,
  name character(55) NOT NULL,
  CONSTRAINT person_type_pkey PRIMARY KEY (id),
  CONSTRAINT person_type_name_key UNIQUE (name)
)

As you can see the id is automatically incremented and the name must be unique. Every time I run an INSERT sql statements, even when it fails, the id is incremented by one.

How can I prevent this? In order that each time a row is inserted successfully, the primary keys are correlative

  • 2
    You can't prevent. That's the whole point of using sequences. And you don't have to care either. The generated id is just some artificial value that has no meaning whatsoever. Whether your artificial key is 1, 42, 36387 or 43873483 is completely irrelevant as long as it is unique. – a_horse_with_no_name Nov 22 '14 at 10:34
  • you could manually set/reset values to sequences, but I doubt it's a good idea. – Mladen Uzelac Nov 22 '14 at 10:39
  • 1
    Basically a dup of stackoverflow.com/q/2036571/398670 . Search for "postgresql gapless sequence" ... and read the manual for nextval which discusses this issue. – Craig Ringer Nov 22 '14 at 11:39
3

this is exactly what you are facing:

test=# CREATE SEQUENCE seq_a;
CREATE SEQUENCE
test=# SELECT nextval('seq_a');
 nextval 
---------
       1
(1 row)

test=# BEGIN;
BEGIN
test=# SELECT nextval('seq_a');
 nextval 
---------
       2
(1 row)

test=# ROLLBACK;
ROLLBACK
test=# SELECT nextval('seq_a');
 nextval 
---------
       3
(1 row)

a sequence makes sure that values are ascending BUT it does not make sure that it does not contain gaps. it is also important to notice that a sequence cannot be rollbacked. you cannot have strictly ascending and gap-free at the same time as it would not work with a mix of long and short transactions. therefore a sequence should never be used for an invoice-id and so on.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.