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 資料去重方法大全》
轉自阿里雲德哥