PostgreSQL 分区表、继承表 记录去重方法-阿里云开发者社区

开发者社区> 数据库> 正文
登录阅读全文

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

简介: postgresql 数据库 去重

背景
当使用数据库分区或继承功能,在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;

(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 数据去重方法大全》
转自阿里云德哥

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

分享:
数据库
使用钉钉扫一扫加入圈子
+ 订阅

分享数据库前沿,解构实战干货,推动数据库技术变革

其他文章