PostgreSQL备份和恢复含有serial的表

最新推荐文章于 2024-08-26 17:11:22 发布

酸奶太酸

最新推荐文章于 2024-08-26 17:11:22 发布

阅读量537

点赞数

分类专栏：文档学习文章标签： postgresql

本文链接：https://blog.csdn.net/Yvelyne/article/details/119614059

版权

文档学习专栏收录该内容

4 篇文章 1 订阅

订阅专栏

文章目录

1. 目的
2. Serial类型
- 2.1 简介
- 2.2 应用
3. 备份和恢复含有serial的表
- 3.1 备份（存档）
- 3.2 恢复（读档）
4. 参考

1. 目的

开发一套工具对一张表进行一系列处理，每步处理依赖于上一步的处理结果。
在开发过程中经常会出现bug，需要倒退回上一步的处理结果，但完全从头开始很浪费时间，因此将每一步的处理结果表进行存档，出现bug时再读档。
在postgres中，通常使用serial类型做为自增id，但读档时serial类型会变成int。为了把类型变回serial查阅了相关的文档和问题，整理如下。

2. Serial类型

2.1 简介

官方文档：Serial
serial类型是一种伪类型，当创建表：

create table test (
id serial,
val float
)

时，等价于创建一个sequence，将其绑定到表上，使id的缺省值为该sequence的下一个值：

create sequence test_id_seq as integer;  -- 除了integer外还可以是smallint,bigint等，决定了serial的范围
create table test (
    id integer NOT NULL DEFAULT nextval('test_id_seq')
);
alter sequence test_id_seq owned by test.id;  -- 绑定，这样删除id列或删除test表时该sequence也会被删除

2.2 应用

如何获得插入后的serial值：有多种方法，最高效简洁的是使用returning。

insert into test(val) values(1) returning id

3. 备份和恢复含有serial的表

以备份和恢复test表为例，id列为serial。备份表为test_backup。

3.1 备份（存档）

drop table if exists test_backup;
select * into test_backup from test;

3.2 恢复（读档）

步骤：
step 1

-- 查询现有id的最大值，后续id将从max_id+1开始
select max(id) from test_backup;

step 2

-- 删除原表
drop table if exists test;
-- 创建表
create table test as (select * from test_backup);
-- 创建sequence
create sequence if not exists test_id_seq;
-- 绑定sequence和表
alter sequence test_id_seq owned by test.id;
-- 设置sequence的起始值（restart）
alter sequence test_id_seq restart with [max_id + 1];
-- 设置主键的default
alter table test alter column id set default nextval('test_id_seq');
-- 设置表的主键
alter table test add primary key (id)

注：alter sequence语句中需要使用restart而不是start。区别（alter sequence）：

在alter语句中使用start with不会改变该sequence现在的值，通过start设置的值只会在下次使用alter sequence restart时作为起始值；
使用restart with [value]会设置该sequence下次被调用nextval()时返回的值为[value]。如果[value]缺省，下次调用时返回的值为create sequence或alter sequence start with时指定的起始值。
举例：test表如下所示，缺省id插入两条数据，现在test_id_seq的值为2。