PostgreSQL 自增主键
随手记录下postgresql使用中的问题
使用的 postgersql version-9.4.24
一、问题重现
在postgresql里实现自增主键时，遇到了设置的主键自增未生效。
postgresql建表语句：
create table "public".dim_shdistrict_info_test(
id SERIAL ,
abb_district varchar(256) ,
district varchar(256) ,
PRIMARY KEY(id, abb_district)
);
comment on table "public".dim_shdistrict_info_test is '上海市行政区划维表';
comment on column "public".dim_shdistrict_info_test."id" is '自增主键';
comment on column "public".dim_shdistrict_info_test."abb_district" is '行政区简称';
comment on column "public".dim_shdistrict_info_test."district" is '行政区';
插入数据方式如下：
insert into dim_shdistrict_info_test
select 0 as id, abb_district, district
from dim_shdistrict_info
dim_shdistrict_info 和 dim_shdistrict_info_test 同表结构
查询结果如下：
相同的数据插入方式，在mysql中可以实现自增，postgresql却不行！
二、mysql 创建自增主键
在mysql数据库中建立自增主键，在建表时，设置AUTO_INCREMENT primary key即可：
建表语句：
create table dim_shdistrict_info_test(
id int(10) AUTO_INCREMENT PRIMARY KEY comment '自增主键',
abb_district varchar(256) comment '行政区简称',
district varchar(256) comment '设备类型描述'
)engine=InnoDB DEFAULT charset=utf8mb4 comment '上海市行政区划维表'
;
插入数据方式如下：
-- 单条数据插入
insert into dim_shdistrict_info_test(abb_district, district)
values
('黄浦', '黄浦区'),
('静安', '静安区'),
('徐汇', '徐汇区'),
('长宁', '长宁区'),
('杨浦', '杨浦区'),
('虹口', '虹口区'),
('普陀', '普陀区'),
('浦东', '浦东新区'),
('宝山', '宝山区'),
('嘉定', '嘉定区'),
('闵行', '闵行区'),
('松江', '松江区'),
('青浦', '青浦区'),
('奉贤', '奉贤区'),
('金山', '金山区'),
('崇明', '崇明区')
;
-- 批量插入
insert into dim_shdistrict_info_test
select 0 as id, abb_district, district
from dim_shdistrict_info
查询结果：
三、postgresql创建自增主键
在 postgresql 中则和mysql数据库略有不同，不再通过 auto_increment 实现，其实现方式主要有三种：serial、sequence 和 identity（10以上版本支持）
1）serial
根据dim_shdistrict_info_test的建表语句，在创建表时会自动创建名为表名_字段名_seq的序列，且MAXVALUE=9223372036854775807，其余值为1。
2）sequence
删除dim_shdistrict_info_test.id，通过sequence方式新建自增主键
alter table "public".dim_shdistrict_info_test DROP COLUMN "id";
alter table "public".dim_shdistrict_info_test add "id" int;
- 创建 sequence
序列名命名规则：表名 + 字段名 + seq
CREATE SEQUENCE "public"."dim_shdistrict_info_test_id_seq"
INCREMENT 1
MINVALUE 1
MAXVALUE 9223372036854775807
START 1
CACHE 1;
SELECT setval('"public"."dim_shdistrict_info_test_id_seq"', 1, false);
ALTER SEQUENCE "public"."dim_shdistrict_info_test_id_seq"
OWNED BY "public"."dim_shdistrict_info_test"."id";
ALTER SEQUENCE "public"."dim_shdistrict_info_test_id_seq" OWNER TO "u_admin";
INCREMENT BY ： 每次序列增加（或减少）的步长
MINVALUE ： 序列最小值，NO MINVALUE表示没有最小值
MAXVALUE ： 序列最大值，NO MAXVALUE表示没有最大值
START WITH ：以什么序列值开始
CYCLE ： 序列是否循环使用
OWNED BY ： 可以直接指定一个表的字段，也可以不指定。
- 设置自增
alter table "public".dim_shdistrict_info_test alter column id set default nextval('dim_shdistrict_info_test_id_seq')
也可在客户端进行参数配置来创建sequence
鼠标右键「设计表」，菜单栏中「其他」选择新建「序列」，配置完成后保存。
打开表设计，选中id列，在默认值填入nextval('dim_shdistrict_info_test_id_seq'::regclass)保存。
3）identity
删除已经建好的自增主键，通过 identity 方式重建（要求版本>=10)
alter table "public".dim_shdistrict_info_test DROP COLUMN "id";
alter table "public".dim_shdistrict_info_test ADD "id" int generated always as identity(cache 100 start with 1 increment by 1) primary key;
四、解决方案
承接重现的问题，最终对插入数据的方式略做修改，完成了自增主键过程，修改如下：
insert into dim_shdistrict_info_test(abb_district, district)
select abb_district, district
from dim_shdistrict_info
参考文档：
https://www.jb51.net/article/204910.htm
https://www.codenong.com/cs107111996/
