文档教程知识库【免费下载链接】til:memo: Today I Learned项目地址https://gitcode.com/gh_mirrors/ti/til点击查看免费下载TRUNCATE是 PostgreSQL 中清空整张表数据的高效手段但很多开发者会发现用serial/bigserial做主键的表被清空后新插入的数据 ID 仍然从上次停下的数字继续递增而不是从 1 重新开始。本文从 til 仓库中 Restarting Sequences When Truncating Tables 这篇笔记出发讲清序列为何不自动重置、TRUNCATE ... RESTART IDENTITY的正确用法以及手动重置序列、CASCADE级联清空等配套实战方案。问题现象TRUNCATE 之后 ID 继续往上数PostgreSQL 的truncate是一个快速清空表数据的特性。当你对一张带serial主键的表执行truncate后数据虽然被清掉了但随后插入的新记录往往会发现 ID 不是从 1 开始而是接着之前的值继续递增。原因在于serial主键背后是一张独立的序列sequence对象而truncate默认并不会重置它。序列记录着下一个要发放的 ID它只负责发号并不关心表中的数据是否还存在。因此清空表之后序列仍停留在之前的推进位置新插入的行自然就接着往下编号。仓库中的另一篇笔记 Restart A Sequence 明确指出了这一现象在开发或测试环境中对表做清空等破坏性操作时主键 ID 的序列just keeps plugging along from where it last left off只会从上次停下的地方继续推进。解决方案给 TRUNCATE 追加 RESTART IDENTITY与其清空后再手动处理序列不如直接告诉 truncate把关联的序列一并重置。在原文档中核心写法如下truncate pokemons, trainers, pokemons_trainers restart identity;只要在truncate语句末尾追加restart identityPostgreSQL 就会在清空表数据的同时将任何关联的序列重置回1。此后向这些表插入新记录主键 ID 会重新从 1 开始。语法要点与可选子句TRUNCATE的完整语法可以概括为TRUNCATE [ TABLE ] 表名 [, ...] [ RESTART IDENTITY | CONTINUE IDENTITY ] [ CASCADE | RESTRICT ]围绕本文主题各子句的含义如下子句作用说明RESTART IDENTITY自动重置关联序列清空数据的同时把所有被这些表引用的序列如serial主键背后的序列重置为 1CONTINUE IDENTITY不重置关联序列这是默认行为即本文开头提到的清空后 ID 继续递增CASCADE级联清空有外键依赖的表会自动把通过外键引用本表的其他表一并清空RESTRICT有外键依赖时报错拒绝默认行为如果其他表通过外键引用了本表truncate会直接报错一条truncate可以同时列出多张表如上面的pokemons, trainers, pokemons_trainersPostgreSQL 会一次性将它们的数据全部清空并统一处理各自关联的序列。一个可复现的完整示例假设有一套宝可梦数据模型pokemons_trainers通过外键关联pokemons与trainers三张表都用serial主键create table pokemons ( id serial primary key, name text not null ); create table trainers ( id serial primary key, name text not null ); create table pokemons_trainers ( id serial primary key, pokemon_id int references pokemons(id), trainer_id int references trainers(id) );先插入几条数据让 ID 推进到一定位置insert into pokemons (name) values (Pikachu), (Charizard), (Squirtle); insert into trainers (name) values (Ash), (Misty); insert into pokemons_trainers (pokemon_id, trainer_id) values (1, 1), (2, 1), (3, 2);此时三张表的序列last_value已经推进到 3 / 2 / 3 左右。接下来执行带restart identity的截断truncate pokemons, trainers, pokemons_trainers restart identity;随后再插入一行测试数据insert into pokemons (name) values (Bulbasaur) returning id;返回的id是1而不是 4——序列已经被成功重置。为什么序列不随事务回滚或清空而重置序列这种自顾自往前走的行为并非偶然。仓库中的 Sequence Side-Effect When Rolling Back Inserts 一文用实验验证了序列的底层机制新建一张带bigserial主键的表books此时books_id_seq的last_value为 1、is_called为f尚未发过号在一个事务里插入 3 行books_id_seq的last_value推进到 3rollback回滚事务后表books恢复了空表但books_id_seq的last_value仍然停留在 3。该笔记给出的解释是序列的使用发生在事务隔离之外。多个并发事务可能同时需要向序列取号为了让它们不必互相阻塞序列始终可以被访问。代价就是序列的last_value只会不断向前推进一旦事务回滚就会留下空洞gap。这也从侧面印证了序列和表数据是两套独立的状态任何只清数据不清序列的操作包括默认的TRUNCATE、普通的DELETE再回滚都不会让 ID 自动归零。restart identity正是针对这一特性提供的一站式解决方案。备选方案一手动重置序列如果你不希望truncate改动序列或者只想单独把某个序列拉回某个值也可以手动重置。仓库中的 Restart A Sequence 给出了ALTER SEQUENCE的写法alter sequence my_table_id_seq restart with 1;执行成功会返回ALTER SEQUENCE。restart with后面的数字可以不是 1——它会把序列的下一次取值设置为任意指定值适合希望清空后从某个特定 ID 继续的场景。需要提醒的是serial主键背后的序列命名遵循表名_列名_seq的约定。例如pokemons表的id列对应的序列叫pokemons_id_seq。如果你重命名过表序列不会跟着自动改名可以参照 Renaming A Sequence 手动执行alter sequence ... rename to ...保持命名一致但无论序列叫什么名字restart identity都能定位并重置它这比手工维护序列名更省心。备选方案二直接 DELETE但要知道代价面对清空整张表的需求另一个常见选择是DELETE FROM 表名不带 WHERE。仓库中的 Truncate All Rows 对比了两者delete from pokemons; -- DELETE 151 truncate pokemons; -- TRUNCATE TABLE结论很明确如果目的就是把表清空TRUNCATE优于DELETE——它直接删除数据而无需逐行扫描速度更快且会立即释放磁盘空间。当然TRUNCATE也意味着更小的后悔余地不可按条件部分删除、不走行级触发器等大规模清空时建议像 Truncate Tables With Dependents 中提醒的那样放进事务里执行以便在必要时回滚。备选方案三处理外键依赖CASCADE现实中的表很少孤立存在。如果一张表被其他表通过外键引用直接truncate它会报错ERROR: cannot truncate a table referenced in a foreign key constraint仓库中的 Truncate Tables With Dependents 给出了两种对策-- 一次性截断相互关联的多张表 truncate A, B; -- 或使用级联自动把依赖表一起清空 truncate A cascade;注意truncate A cascade执行时 PostgreSQL 会返回NOTICE: truncate cascades to table B提醒你哪些表被波及。当这种清空主表并重置所有相关自增 ID的需求出现时将cascade与restart identity组合使用truncate A cascade restart identity;即可一次完成级联清空 全链路序列归零。进阶思考serial 的现代替代方案 identity 列既然serial主键的序列管理如此黏人仓库中的 Generate Modern Primary Key Columns 指出PostgreSQL 社区早已不再推荐用serial定义新表的主键而是推荐使用标准 SQL 的identity 列create table books ( id int primary key generated always as identity, title text not null, author text not null, created_at timestamptz not null default now(), updated_at timestamptz not null default now() );identity 列在底层同样依赖序列因此truncate ... restart identity对它同样生效但它把序列的创建、依赖关系和权限管理与列本身绑定得更规范规避了serial在 schema、依赖和权限管理上的weird behaviors。对于正在设计新表的读者建议直接使用 identity 列对于存量serial表restart identity依然是清空后重置 ID 的最直接手段。小结默认的TRUNCATE只清数据、不动序列serial主键清空后 ID 会继续递增因为序列是独立于表数据推进的在TRUNCATE末尾追加RESTART IDENTITY即可在清空数据的同时把所有关联序列重置为 1序列取值发生在事务隔离之外回滚插入也无法让序列回退这是设计使然详见 Sequence Side-Effect When Rolling Back Inserts需要手动精确控制时可用ALTER SEQUENCE ... RESTART WITH n参考 Restart A Sequence面对外键依赖CASCADE与RESTART IDENTITY可以组合使用参考 Truncate Tables With Dependents新项目建议用 identity 列替代serial参考 Generate Modern Primary Key Columns。掌握restart identity你就能在开发、测试环境里放心地反复清空并重灌数据不必再为ID 越数越大而烦恼。赞分享文档教程知识库【免费下载链接】til:memo: Today I Learned项目地址https://gitcode.com/gh_mirrors/ti/til点击查看免费下载相关推荐Formily Vue 自定义组件中的 RecursionField 自增列表递归实战从 useField 到递归渲染Formily Vue 自定义组件中的 RecursionField 自增列表递归实战从 useField 到递归渲染 导读 本文聚焦 Formily 的 V前端UI组件drizzle-orm 0.32.0-beta 新特性实战PostgreSQL 序列与 Identity 列、全方言 Generated 列及 Drizzle Kit 迁移增强drizzle orm 0.32.0 beta 新特性实战PostgreSQL 序列与 Identity 列、全方言 Generated 列及 Drizzle后端数据库ORMkubectl-ai安全最佳实践API密钥管理与权限控制详解kubectl ai安全最佳实践API密钥管理与权限控制详解 kubectl ai作为一款结合OpenAI GPT能力的Kubernetes插件在提升工作效上一篇MaaYuan智能自动化工具游戏日常任务的高效解放方案下一篇CPython 字典观察者 API 在自由线程构建下的线程安全增强gh-issue-145235创作声明：本文部分内容由AI辅助生成（AIGC），仅供参考