作者
digoal
日期
2018-10-22
标签
PostgreSQL , 分区表 , 继承表 , 去重 , only
背景
当使用数据库分区或继承功能，在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 许愿链接
您的愿望将传达给PG kernel hacker、数据库厂商等, 帮助提高数据库产品质量和功能, 说不定下一个PG版本就有您提出的功能点. 针对非常好的提议，奖励限量版PG文化衫、纪念品、贴纸、PG热门书籍等，奖品丰富，快来许愿。开不开森.
9.9元购买3个月阿里云RDS PostgreSQL实例
PostgreSQL 解决方案集合
德哥 / digoal's github - 公益是一辈子的事.
