PostgreSQL Data Rotate Method Introduction: Overwrite History Data by Time

Background

Example

Method

create table test0(like test including all, constraint ck check(extract(dow from crt_time)=0)) inherits(test);  
create table test1(like test including all, constraint ck check(extract(dow from crt_time)=1)) inherits(test);
create table test2(like test including all, constraint ck check(extract(dow from crt_time)=2)) inherits(test);
create table test3(like test including all, constraint ck check(extract(dow from crt_time)=3)) inherits(test);
create table test4(like test including all, constraint ck check(extract(dow from crt_time)=4)) inherits(test);
create table test5(like test including all, constraint ck check(extract(dow from crt_time)=5)) inherits(test);
create table test6(like test including all, constraint ck check(extract(dow from crt_time)=6)) inherits(test);

create table test_def(like test including all) inherits(test);
postgres=#  explain select * from test where crt_time=now() and extract(dow from crt_time)=0; 
QUERY PLAN
-----------------------------------------------------------------------------------------------------
Append (cost=0.00..65.20 rows=3 width=44)
-> Seq Scan on test (cost=0.00..0.00 rows=1 width=44)
Filter: ((crt_time = now()) AND (date_part('dow'::text, crt_time) = '0'::double precision))
-> Seq Scan on test0 (cost=0.00..32.60 rows=1 width=44)
Filter: ((crt_time = now()) AND (date_part('dow'::text, crt_time) = '0'::double precision))
-> Seq Scan on test_def (cost=0.00..32.60 rows=1 width=44)
Filter: ((crt_time = now()) AND (date_part('dow'::text, crt_time) = '0'::double precision))
(7 rows)
create or replace function ins_tg() returns trigger as $$
declare
begin
case extract(dow from NEW.crt_time)
when 0 then insert into test0 values (NEW.*);
when 1 then insert into test1 values (NEW.*);
when 2 then insert into test2 values (NEW.*);
when 3 then insert into test3 values (NEW.*);
when 4 then insert into test4 values (NEW.*);
when 5 then insert into test5 values (NEW.*);
when 6 then insert into test6 values (NEW.*);
end case;
return null;
end;
$$ language plpgsql strict;
create trigger tg before insert on test for each row execute procedure ins_tg();insert into test values (1,'test',now()+interval '1 day');
insert into test values (1,'test',now()+interval '2 day');
insert into test values (1,'test',now()+interval '3 day');
insert into test values (1,'test',now()+interval '4 day');
insert into test values (1,'test',now()+interval '5 day');
insert into test values (1,'test',now()+interval '6 day');
insert into test values (1,'test',now()+interval '7 day');
postgres=# select tableoid::regclass , * from test;
tableoid | id | info | crt_time
----------+----+------+----------------------------
test0 | 1 | test | 2017-03-26 14:40:48.066905
test1 | 1 | test | 2017-03-27 14:40:50.450942
test2 | 1 | test | 2017-03-28 14:40:52.271922
test4 | 1 | test | 2017-03-23 14:40:22.551928
test5 | 1 | test | 2017-03-24 14:40:24.643933
test6 | 1 | test | 2017-03-25 14:40:28.138913
test3 | 1 | test | 2017-03-22 14:40:20.586945
(7 rows)
1. 开始事务  
begin;

2. 设置锁超时
set lock_timeout = '60s';

3. 查询明天的dow
select extract(dow from current_date+1);
date_part
-----------
3
(1 row)

4. 清除test_def约束
alter table test_def drop constraint IF EXISTS ck;
5. 清除test_def数据
truncate test_def;

6. 重命名test_def
alter table test_def rename to test_def_tmp;

7. 重命名明天的分区表
alter table test3 rename to test_def;

8. test_def_tmp添加约束(明天)
alter table test_def_tmp add constraint ck check(extract(dow from crt_time)=3);

9. test_def_tmp重命名为明天的分区
alter table test_def_tmp rename to test3;

10. 提交或回滚
commit;
如果失败,回滚事务。
Rollback;

Summary

Original Source:

--

--

Follow me to keep abreast with the latest technology news, industry insights, and developer trends. Alibaba Cloud website:https://www.alibabacloud.com

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Alibaba Cloud

Follow me to keep abreast with the latest technology news, industry insights, and developer trends. Alibaba Cloud website:https://www.alibabacloud.com