PostgreSQL 分区表、继承表 记录去重方法

2 minute read

背景

当使用数据库分区或继承功能,在PK层面上出现分区与分区,或分区与主表出现了重复的键值时,可以通过tableoid进行甄别,同时通过ONLY TABLE的操作方法进行删除。

select tableoid::regclass  
  
delete|select|update|truncate only  

例子

创建测试表、继承分区,PK约束在独立的分区或主表上

postgres=# create table p (id int primary key, info text, crt_time timestamp);  
CREATE TABLE  
postgres=# create table p0 (like p including all) inherits(p);  
NOTICE:  merging column "id" with inherited definition  
NOTICE:  merging column "info" with inherited definition  
NOTICE:  merging column "crt_time" with inherited definition  
CREATE TABLE  
postgres=# create table p1 (like p including all) inherits(p);  
NOTICE:  merging column "id" with inherited definition  
NOTICE:  merging column "info" with inherited definition  
NOTICE:  merging column "crt_time" with inherited definition  
CREATE TABLE  
postgres=# create table p2 (like p including all) inherits(p);  
NOTICE:  merging column "id" with inherited definition  
NOTICE:  merging column "info" with inherited definition  
NOTICE:  merging column "crt_time" with inherited definition  
CREATE TABLE  
postgres=# create table p3 (like p including all) inherits(p);  
NOTICE:  merging column "id" with inherited definition  
NOTICE:  merging column "info" with inherited definition  
NOTICE:  merging column "crt_time" with inherited definition  
CREATE TABLE  

往不同的分区写入PK重复的数据

postgres=# insert into p values (1,'test',now());  
INSERT 0 1  
postgres=# insert into p0 values (1,'test',now());  
INSERT 0 1  
postgres=# insert into p1 values (1,'test',now());  
INSERT 0 1  
postgres=# insert into p2 values (1,'test',now());  
INSERT 0 1  

查询,你可能会不知道记录属于哪个表

postgres=# select * from p;  
 id | info |          crt_time            
----+------+----------------------------  
  1 | test | 2018-10-22 09:26:55.456769  
  1 | test | 2018-10-22 09:26:58.441338  
  1 | test | 2018-10-22 09:27:01.149731  
  1 | test | 2018-10-22 09:27:03.389089  
(4 rows)  

通过tableoid进行甄别

postgres=# select tableoid::regclass,* from p;  
 tableoid | id | info |          crt_time            
----------+----+------+----------------------------  
 p        |  1 | test | 2018-10-22 09:26:55.456769  
 p0       |  1 | test | 2018-10-22 09:26:58.441338  
 p1       |  1 | test | 2018-10-22 09:27:01.149731  
 p2       |  1 | test | 2018-10-22 09:27:03.389089  
(4 rows)  

直接指定PK删除主表时,会将所有记录删除。

postgres=# delete from p where id=1;  
DELETE 4  
postgres=# select tableoid::regclass,* from p;  
 tableoid | id | info | crt_time   
----------+----+------+----------  
(0 rows)  

delete|select|update|truncate only 清除指定分区的数据

https://www.postgresql.org/docs/11/static/sql-delete.html

通过only关键字,可以指定只操作当前表,不包括继承或子继承的表.

postgres=# insert into p values (1,'test',now());  
INSERT 0 1  
postgres=# insert into p0 values (1,'test',now());  
INSERT 0 1  
postgres=# insert into p1 values (1,'test',now());  
INSERT 0 1  
postgres=# insert into p2 values (1,'test',now());  
INSERT 0 1  
postgres=# delete from only p where id=1;  
DELETE 1  
postgres=# select tableoid::regclass,* from p;  
 tableoid | id | info |          crt_time            
----------+----+------+----------------------------  
 p0       |  1 | test | 2018-10-22 09:27:47.510151  
 p1       |  1 | test | 2018-10-22 09:27:49.366293  
 p2       |  1 | test | 2018-10-22 09:27:51.255673  
(3 rows)  
  
postgres=# delete from only p2 where id=1;  
DELETE 1  
postgres=# select tableoid::regclass,* from p;  
 tableoid | id | info |          crt_time            
----------+----+------+----------------------------  
 p0       |  1 | test | 2018-10-22 09:27:47.510151  
 p1       |  1 | test | 2018-10-22 09:27:49.366293  
(2 rows)  

如果是单张表内的数据去重,请参考末尾连接。

参考

《PostgreSQL 数据去重方法大全》

Flag Counter

digoal’s 大量PostgreSQL文章入口