PostgreSQL主键自增正确配置方法及初始化插入ID全为0故障排查

问题原因 #

• 冗余的自增序列操作：SERIAL 是PostgreSQL的自增语法糖，创建表时会自动生成名为cities_id_seq的专属序列，同时自动将id列的默认值设为该序列的nextval返回值，你额外新建cities_seq_id并修改默认值的操作完全多余，反而会引发序列归属、权限问题。
• 空表下setval执行失败：你执行SELECT setval('cities_seq_id', (SELECT max(id)+1 FROM cities), false)时，cities表是空的，max(id)返回NULL，max(id)+1也是NULL，setval传入非法值会执行报错，导致后续修改id默认值的语句中断，id列无有效自增默认值。
• 潜在的SQL语法错误：PostgreSQL不支持//作为单行注释（仅支持--单行注释、/* */块注释），如果你SQL文件中保留了// 1.这类注释行，会直接触发语法错误，导致整个SQL文件的后续语句都不会执行。
• 序列权限缺失：你仅给testapi用户授予了数据库级权限，自定义的cities_seq_id需要单独授予USAGE权限，否则调用nextval获取序列值会失败，无有效默认值时，部分ORM或客户端会自动给id补0后插入。

解决方法 #

1. 清理旧容器和残留数据：
   执行以下命令删除旧容器，避免之前的错误表结构残留：

# 停止并删除旧容器
docker rm -f postgres
# 可选：如果挂载了持久化卷，一并删除对应卷
docker volume prune

2. 修正SQL脚本：
   去掉多余的自定义序列操作，修正注释语法，修正后的3个SQL文件内容如下：

• 授权SQL：

GRANT ALL PRIVILEGES ON DATABASE testdb TO testapi;

• 建表SQL：

-- 建表：SERIAL自带自增序列，主键自带唯一约束，无需额外加UNIQUE
CREATE TABLE IF NOT EXISTS cities (
    id SERIAL PRIMARY KEY,
    name VARCHAR (50) UNIQUE NOT NULL
);

-- 兼容表已有历史数据的场景，空表时默认序列从1开始
SELECT setval('cities_id_seq', COALESCE((SELECT max(id) FROM cities), 1), false);

• 插入数据SQL无需修改，保持原有内容即可。

3. 确认SQL文件命名顺序：
   docker的docker-entrypoint-initdb.d目录下的脚本会按文件名的字母/数字顺序执行，确保3个文件的执行顺序为：授权SQL -> 建表SQL -> 插入数据SQL，比如命名为1_grant.sql、2_create_table.sql、3_insert_data.sql。
4. 重新启动容器，执行原有的docker启动命令即可，启动完成后可执行docker logs postgres查看初始化日志确认无报错，直接连接数据库执行SELECT * FROM cities;即可看到id列从1开始依次递增。

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