Pgsql自增ID设置及相关问题

常用命令

create sequence network.error_statistics_id_seq start with 1 increment by 1 no minvalue no maxvalue cache 1;

SELECT * FROM pg_class WHERE relkind = 'S';

alter table error_statistics alter column id set default nextval('network.error_statistics_id_seq');

方法一：设置自增ID（推荐）

-- 设置一个规则

create sequence [schema.table_id_seq] start with 1 increment by 1 no minvalue no maxvalue cache 1;

示例

create sequence [network.network_monitor_group_id_seq] start with 1 increment by 1 no minvalue no maxvalue cache 1;

create sequence [log.log_id_seq] start with 1 increment by 1 no minvalue no maxvalue cache 1;

不指定schema，会使用默认的schema public

-- 把这个规则给指定字段

alter table [log] alter column id set default nextval('log_id_seq');

alter table [log] alter column id set default nextval('schema.log_id_seq');

方法二：新增，增加自增主键ID

对于没有主键的Postgresql数据表，执行以下语句即可增添自增的主键列id：

ALTER TABLE [table_name] ADD COLUMN id BIGSERIAL PRIMARY KEY;

方法三：建表, 创建表时设置自增ID

CREATE TABLE "error_log" (
  "id" BIGSERIAL NOT NULL,
  "ip" char(16),
  "logTime" timestamp,
  "description" varchar(255),
  "message" text,
  PRIMARY KEY ("id")
);

注意：

1. 数据量小的情况下，也可以用SERIAL替代BIGSERIAL
2. 可以add column id BIGSERIAL PRIMARY KEY;，但不可以alter column id BIGSERIAL PRIMARY KEY;

通过方法二建表如下

CREATE TABLE epictable
(
mytable_key  serial primary key,
moobars    VARCHAR(40) not null,
foobars    DATE
);

查看最终的建表语句，如下

CREATE TABLE "public"."epictable" (

"mytable_key" int4 NOT NULL DEFAULT nextval('epictable_mytable_key_seq'::regclass),
"moobars" varchar(40) COLLATE "pg_catalog"."default" NOT NULL,
"foobars" date,
CONSTRAINT "epictable_pkey" PRIMARY KEY ("mytable_key")

)

查看所有sequence

说明方法二只是把方法一的手动过程改为自动化，但其本质还是一样的。而方法一其实更灵活，数据迁移过程中更加便于保留原有id，及维持原表之间的外键关系。

经测试，如果id为自增，当前值为5，插入一条数据id手动设置为8，再插入一条数据，自增id还是从5开始计数。当自增id增长为8时报错，数据插入成功，id自动更改为9.
也就是说，自增id的计算，不会参考当前表的最大值，插入遇到问题自增修正。

#查询所有sequence

SELECT "c"."relname" FROM "pg_class" "c" WHERE "c"."relkind" = 'S';

#查询sequence的当前数值

不会导致sequence自增

select currval('node_id'::regclass)

#查询sequence的下一个数值

会导致sequence自增，慎用

select nextval('network.mapping_id'::regclass)

#设置sequence

select setval('node_id', (select max(id) from network.network_monitor_node));

#删除sequence

DROP SEQUENCE test_c_id_seq;

级联删除

drop SEQUENCE network.mapping_id CASCADE

参考

PostgreSQL Sequence序列的使用详解PostgreSQL脚本之家

https://www.jb51.net/article/205227.htm

postgresql主键自增 - 我命由我不由天—hao - 博客园

https://www.cnblogs.com/haolb123/p/16553102.html

(130条消息) PostgreSQL之Sequence序列_风情客家__的博客-CSDN博客_postgresql sequence

https://blog.csdn.net/justlpf/article/details/84818198

PGSQL创建自增的id-- postgresql nextval 使用 - 走看看

http://t.zoukankan.com/youyou0-p-12651834.html