sql - sql表中主键id之间的差距

Question

我有一个表是:

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;

当我对唯一列的相同值插入错误请求时。 “id”正在增加...这是错误的 id 请求；

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 **********

这是我的表格结果；

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"

请帮助我如何解决这个问题，我想要像 1,2,3. 这样的顺序。顺序..

Answer 1

这是路sequences工作。

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.

正如评论中所指出的，序列中存在间隙没有坏处。如果您出于某种原因删除了表中的某些行，您会在主键值中产生间隙，并且您通常不会费心重置它们以使它们顺序排列。

如果您坚持要创建一个无缝序列，请阅读这篇文章:http://www.varlena.com/GeneralBits/130.php并为慢速插入做好准备。