Search code examples
sqlpostgresql

Gaps between primary key id in sql table

I have a table which is:

CREATE SEQUENCE id_seq;
CREATE TABLE public."UserInfo"
(
  id bigint NOT NULL DEFAULT nextval('id_seq'),
  phone text,
  password text,
  name text,
  surname text,
  middle_name text,
  email text,
  company text,
  title text,
  image_id text,
  CONSTRAINT "UserInfo_pkey" PRIMARY KEY (id),
  CONSTRAINT "UserInfo_image_id_key" UNIQUE (image_id),
  CONSTRAINT "UserInfo_phone_key" UNIQUE (phone)
)
WITH (
  OIDS=FALSE
);
ALTER SEQUENCE id_seq OWNED BY public."UserInfo".id;
ALTER TABLE public."UserInfo"
  OWNER TO postgres;

When I make bad request for insert like same value for unique column. "id" is increasing... Here is bad id request;

ERROR:  null value in column "id" violates not-null constraint
DETAIL:  Failing row contains (null, 9921455867, mg123209, name, surname, , [email protected], Company Name, Title Of Person, 123asd).
********** Error **********

Here is my table result ; 

1;"1234477867";"qweff";"Name";"Surname";"''";"[email protected]";"Company";"Title";"qwer1234"
4;"5466477868";"1235dsf";"Name";"Surname";"''";"[email protected]";"Company";"Title";"qwer1235"
6;"5051377828";"asd123";"Name";"Surname";"''";"[email protected]";"Company";"Title";"qwesr1235"

Please help me how I can solve this issue, I want order like 1,2,3.. sequential..

Solution

  • This is the way sequences work.

    Important: To avoid blocking concurrent transactions that obtain numbers from the same sequence, a nextval operation is never rolled back; that is, once a value has been fetched it is considered used and will not be returned again. This is true even if the surrounding transaction later aborts, or if the calling query ends up not using the value.

    As pointed out in the comments there's no harm in having gaps in sequences. If you delete some rows in your table for what ever reason you are creating gaps in your primary key values and you wouldn't normally bother with resetting them to make them sequential.

    If you insist on creating a gapless sequence, read this article: https://web.archive.org/web/20180110111154/http://www.varlena.com/GeneralBits/130.php and be prepared for slow inserts.