Postgresql数据库为主键创建自增序列

1.创建表的序列

CREATE SEQUENCE "public"."sys_role_id_seq" 
INCREMENT 1
MINVALUE  1
MAXVALUE 9223372036854775807
START 1
CACHE 1;

说明

• “public”：模式名。（参考：PostgreSQL 模式（SCHEMA））
• “sys_role_id_seq” ：序列名
• INCREMENT ：自增量
• MINVALUE：最小值
• MAXVALUE：最大值
• START：起始值
• CACHE：预分配值的个数

2.创建表

DROP TABLE IF EXISTS "public"."sys_role";
CREATE TABLE "public"."sys_role" (
"id" int8 DEFAULT nextval('sys_role_id_seq'::regclass) NOT NULL,
"gmt_create" date,
"gmt_modified" date,
"create_user_id" int8,
"modified_user_id" int8,
"role_name" varchar(64) COLLATE "default",
"role_describe" varchar(64) COLLATE "default",
"org_id" int8,
"area_code" varchar(16) COLLATE "default",
"status" int4
)
WITH (OIDS=FALSE)
;
ALTER TABLE "public"."sys_role" OWNER TO "postgres";

COMMENT ON TABLE "public"."sys_role" IS '系统_角色表';
COMMENT ON COLUMN "public"."sys_role"."id" IS '序号';
COMMENT ON COLUMN "public"."sys_role"."gmt_create" IS '创建时间';
COMMENT ON COLUMN "public"."sys_role"."gmt_modified" IS '修改时间';
COMMENT ON COLUMN "public"."sys_role"."create_user_id" IS '创建人id';
COMMENT ON COLUMN "public"."sys_role"."modified_user_id" IS '修改人id';
COMMENT ON COLUMN "public"."sys_role"."role_name" IS '角色名称';
COMMENT ON COLUMN "public"."sys_role"."role_describe" IS '角色描述';
COMMENT ON COLUMN "public"."sys_role"."org_id" IS '组织机构id';
COMMENT ON COLUMN "public"."sys_role"."area_code" IS '行政区';
COMMENT ON COLUMN "public"."sys_role"."status" IS '0:有效;1:无效';

3.设置主键

ALTER TABLE "public"."sys_role" ADD PRIMARY KEY ("id");

4.设置主键自增

ALTER SEQUENCE "public"."sys_role_id_seq" OWNED BY "public"."sys_role"."id";

5.设置序列权属的角色

ALTER SEQUENCE "public"."sys_role_id_seq" OWNER TO "postgres";

参考文章：
1、postgresql创建序列查询序列
2、PostgreSQL如何为主键创建自增序列(Sequences)
3、【数据库】postgresql数据库创建自增序列id的注意事项
另 无关：13.pgsql中的用户、角色、权限