背景

当使用数据库分区或继承功能，在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 清除指定分区的数据

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