1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45

-- -- 准备阶段， 删除表
drop table TABLE_Original;
drop table TABLE_History;

-- 创建 原始表， 模拟数据
create table TABLE_Original(ca varchar(10), createtime timestamp)
insert into TABLE_Original(ca,createtime) values('r1','2024-03-10');
insert into TABLE_Original(ca,createtime) values('r1','2024-03-11');
insert into TABLE_Original(ca,createtime) values('r1','2024-03-12');
insert into TABLE_Original(ca,createtime) values('r1','2024-03-13');

-- 创建备份表
create table TABLE_History(ca varchar(10),createtime timestamp)


-- -- 转移数据 **指定列数据**
-- -- moved_rows 是临时表
-- WITH moved_rows AS (
--     DELETE FROM TABLE_Original where createtime < '2024-03-12'
--     RETURNING *
-- )
-- INSERT INTO TABLE_History -- (ca,createtime)
-- SELECT ca,createtime FROM moved_rows;

-- 转移 **所有数据**
-- moved_rows 是临时表
--
-- 操作流程
-- 1. 创建 history 表， 保证与 Original 表结构一致
-- 2. 使用时间变量控制转移的数据
-- 3. 定时任务将数据转移 History 表
--
-- 好处
-- 1. 主表每天数量大体一致， 不影响效率。
-- 2. 每天的定时任务， 比每年的定时任务， 更容易控制。
-- 3. 历史数据可以随时查询。
-- 4. 历史数据可以定期进行数据删除/archive， 不影响主表业务。
-- 5. 一条命令， 具有事务性。

WITH moved_rows AS (
    DELETE FROM TABLE_Original where createtime < '2024-03-12'
    RETURNING *
)
INSERT INTO TABLE_History
SELECT * FROM moved_rows;