Gaps between primary key id in sql table

Question

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, , namesurname@xxx.com, Company Name, Title Of Person, 123asd).
********** Error **********

Here is my table result ;

1;"1234477867";"qweff";"Name";"Surname";"''";"namesurname@qwe.com";"Company";"Title";"qwer1234"
4;"5466477868";"1235dsf";"Name";"Surname";"''";"banesyrna@pwqe.com";"Company";"Title";"qwer1235"
6;"5051377828";"asd123";"Name";"Surname";"''";"qweg@sdcv.com";"Company";"Title";"qwesr1235"

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

This is how sequences work. There is nothing wrong with gaps in generated primary key values. There is nothing you need to do, everything is fine. — , Aug 23 '16 at 11:30
Why not use `SERIAL`? http://stackoverflow.com/questions/787722/postgresql-autoincrement — HoneyBadger, Aug 23 '16 at 11:33
@a_horse_with_no_name, @KamilG, So? Is there a reason not to use `SERIAL`? Seems a bit cleaner to me (note: I have no experience with `postgres`) — HoneyBadger, Aug 23 '16 at 11:36
@HoneyBadger the way you've phrased your comment indicates that these things are different, while this actually is a `serial` column (it does explicitly what `serial` does under the hood). — Kamil Gosciminski, Aug 23 '16 at 11:41
@HoneyBadger: if you create a table with a `serial` and then use `pg_dump` to get its definition it will actually output the SQL code in the question. `serial` is just a macro. But that is not the point of the question. — , Aug 23 '16 at 11:41
@a_horse_with_no_name so in order to have gaps between id, `uuid ` is better than bigint — goGud, Aug 23 '16 at 11:43

score 3 · Accepted Answer · answered Aug 23 '16 at 11:46

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: http://www.varlena.com/GeneralBits/130.php and be prepared for slow inserts.

score 2 · Answer 2 · answered Aug 23 '16 at 12:00

When generating an artificial primary key of this type it's important to clearly understand that the value of the primary key has no meaning. I'll emphasize that point again - THE VALUE OF THE KEY HAS NO MEANING, and any "meaning" which is ascribed to it by developers, managers, or users is incorrect. It's a value which is unique, non-null, and unchanging - that's it. It does not matter what the number is. It does not matter if it's in some "order" relative to other keys in the table. Do not fall into the trap of believing that these values need to be ordered, need to be increasing, or must in some other manner conform to some external expectations of "how they should look".

Unique. Non-null. Unchanging. That's all you know, and all you need know.

Best of luck.

`artificial primary key` = `surrogate key` – MatBailie Aug 23 '16 at 12:27 — MatBailie, Aug 23 '16 at 12:27

Gaps between primary key id in sql table

2 Answers2

Linked