PostgreSQL中serial序列跨表共用问题：如何实现各表独立自增？

嘿，我来帮你搞定这个问题！咱们分步骤来操作，确保最终两张表的主键各自独立、按顺序连续自增：

一、先备份数据（绝对不能省！） #

在动手修改之前，一定要先备份两张表的数据，万一操作出问题还能快速恢复：

-- 备份words表
CREATE TABLE words_backup AS SELECT * FROM words;
-- 备份definitions表
CREATE TABLE definitions_backup AS SELECT * FROM definitions;

二、把现有数据的主键调整为连续序列 #

现在你的words表wordid是1、2、5，definitions表definitionid是3、4、6、7，咱们先把这些现有id改成连续的：

1. 处理words表 #

-- 先添加一个临时列，用来存储新的连续id
ALTER TABLE words ADD COLUMN new_wordid INT;

-- 用ROW_NUMBER()生成从1开始的连续id，按原wordid排序保证顺序不变
UPDATE words
SET new_wordid = ROW_NUMBER() OVER (ORDER BY wordid);

-- 先检查新id是否符合预期，没问题再继续下一步
SELECT wordid, new_wordid FROM words ORDER BY wordid;

-- 替换原主键列（如果definitions表有外键关联到wordid，要先更新definitions里的外键值！）
-- 有外键的话先执行这句：UPDATE definitions d SET wordid = w.new_wordid FROM words w WHERE d.wordid = w.wordid;
ALTER TABLE words DROP COLUMN wordid;
ALTER TABLE words RENAME COLUMN new_wordid TO wordid;
ALTER TABLE words ALTER COLUMN wordid SET NOT NULL;
ALTER TABLE words ADD PRIMARY KEY (wordid);

2. 处理definitions表 #

用同样的逻辑，把definitionid改成连续序列：

-- 添加临时列
ALTER TABLE definitions ADD COLUMN new_definitionid INT;

-- 生成连续id
UPDATE definitions
SET new_definitionid = ROW_NUMBER() OVER (ORDER BY definitionid);

-- 验证结果是否正确
SELECT definitionid, new_definitionid FROM definitions ORDER BY definitionid;

-- 替换原列（如果有其他表关联这个主键，记得先更新关联表的外键值！）
ALTER TABLE definitions DROP COLUMN definitionid;
ALTER TABLE definitions RENAME COLUMN new_definitionid TO definitionid;
ALTER TABLE definitions ALTER COLUMN definitionid SET NOT NULL;
ALTER TABLE definitions ADD PRIMARY KEY (definitionid);

三、给每张表创建独立的自增序列 #

现在要解决序列共用的问题，给两张表分别创建专属序列，确保后续自增互不影响：

1. 为words表创建专属序列 #

-- 创建序列，起始值设为当前words表最大id+1（比如现在最大是3，就从4开始自增）
CREATE SEQUENCE words_wordid_seq
START WITH (SELECT MAX(wordid) + 1 FROM words)
INCREMENT BY 1
OWNED BY words.wordid;

-- 把wordid的默认值绑定到这个新序列
ALTER TABLE words ALTER COLUMN wordid SET DEFAULT nextval('words_wordid_seq');

2. 为definitions表创建专属序列 #

-- 创建序列，起始值设为当前definitions表最大id+1（比如现在最大是4，就从5开始）
CREATE SEQUENCE definitions_definitionid_seq
START WITH (SELECT MAX(definitionid) + 1 FROM definitions)
INCREMENT BY 1
OWNED BY definitions.definitionid;

-- 绑定默认值到新序列
ALTER TABLE definitions ALTER COLUMN definitionid SET DEFAULT nextval('definitions_definitionid_seq');

四、测试效果 #

现在插几条测试数据，验证两张表的主键是否独立、连续自增：

-- 给words插两条数据，应该得到4、5
INSERT INTO words (wordid) VALUES (DEFAULT);
INSERT INTO words (wordid) VALUES (DEFAULT);

-- 给definitions插两条数据，应该得到5、6
INSERT INTO definitions (definitionid) VALUES (DEFAULT);
INSERT INTO definitions (definitionid) VALUES (DEFAULT);

-- 查看结果，确认id是连续且独立的
SELECT * FROM words ORDER BY wordid;
SELECT * FROM definitions ORDER BY definitionid;

补充：如果原来存在那个共用的旧序列，确认新序列正常工作后，可以删掉它（先确保没有其他表还在引用哦）：

DROP SEQUENCE IF EXISTS 旧序列名; -- 把这里换成你实际的旧序列名称

内容的提问来源于stack exchange，提问作者emert117