PostgreSQL裁判表ID为负数问题及修改需求

一、ID正负差异的可能原因 #

从你提供的表结构SQL来看，两张表的ID字段仅定义为bigint NOT NULL主键，没有显式绑定序列或设置默认值。出现正负差异的常见原因有几个：

• 序列起始值配置不同：如果球员表的ID字段通过SERIAL/IDENTITY或者手动绑定了从1开始的序列，而裁判表绑定的序列被设置为从-1起始，就会导致生成的ID一正一负。
• 插入数据时手动指定ID：在插入裁判数据时，可能手动输入了负数ID，或者应用程序生成ID的逻辑对球员和裁判采用了不同规则（比如裁判ID从-1倒序生成）。
• 数据迁移/导入时的问题：如果裁判表数据是从外部导入的，原数据的ID就是负数，导入时没有做转换。

二、将裁判表负数ID修改为正数的步骤 #

1. 先检查关联约束 #

首先确认是否有其他表通过外键关联td_referee.id_referee，如果有，需要同步修改关联表的外键值，或者临时禁用外键约束（操作前务必备份数据）。

2. 修改现有负数ID #

根据实际需求选择以下两种方式：

方式一：直接取ID的绝对值

适用于负数ID的绝对值没有重复的情况：

-- 先检查是否有重复的绝对值（避免主键冲突）
SELECT ABS(id_referee) AS abs_id, COUNT(*)
FROM i_match.td_referee
GROUP BY abs_id
HAVING COUNT(*) > 1;

如果查询无结果，执行更新：

UPDATE i_match.td_referee
SET id_referee = ABS(id_referee);

方式二：重新分配连续的正数ID

如果负数ID的绝对值存在重复，或者希望ID从1开始连续排列：

WITH referee_new_ids AS (
    SELECT 
        id_referee,
        ROW_NUMBER() OVER (ORDER BY birth_date, last_name) AS new_id
    FROM i_match.td_referee
)
UPDATE i_match.td_referee r
SET id_referee = rn.new_id
FROM referee_new_ids rn
WHERE r.id_referee = rn.id_referee;

注：ORDER BY子句可根据你的需求调整排序规则，确保ID分配符合业务逻辑。

3. 调整序列（如果ID由序列生成） #

如果id_referee是通过序列自动生成的，需要修改序列的起始值，避免后续继续生成负数ID：

-- 先查询对应的序列名称
SELECT pg_get_serial_sequence('i_match.td_referee', 'id_referee');

-- 将序列重置为当前最大ID+1（替换下面的【序列名称】）
ALTER SEQUENCE 【序列名称】 RESTART WITH (SELECT MAX(id_referee) + 1 FROM i_match.td_referee);

4. 验证修改结果 #

执行以下语句确认所有ID已转为正数：

SELECT * FROM i_match.td_referee WHERE id_referee < 1;

正常情况下应返回空结果集。

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