PostgreSQLでパーティションテーブルを作ると、テーブル一覧にはパーティション元テーブル(親テーブル)に加え、パーティションテーブル(子テーブル)も表示されます。それらを関連付けて表示するメモ。
検証環境
postgres=# select version(); version ------------------------------------------------------------------------------------------------------------------ PostgreSQL 12.4 (Debian 12.4-1.pgdg100+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 8.3.0-6) 8.3.0, 64-bit (1 行)
今回作成するパーティションテーブル
元データは全国地方公共団体コード(https://www.soumu.go.jp/denshijiti/code.html)です。
create table zenkoku_chiho_kokyodantai_code ( dantai_code varchar(6) ,todofuken_kanji varchar(4) ,shichoson_kanji varchar(10) ,todofuken_kana varchar(30) ,shichoson_kana varchar(30) ) partition by list (todofuken_kanji) ; create table zenkoku_chiho_kokyodantai_code_1 partition of zenkoku_chiho_kokyodantai_code for values in ('北海道'); create table zenkoku_chiho_kokyodantai_code_2 partition of zenkoku_chiho_kokyodantai_code for values in ('青森県'); create table zenkoku_chiho_kokyodantai_code_3 partition of zenkoku_chiho_kokyodantai_code for values in ('岩手県'); ...
データ例はこんな感じです。
010006,北海道,,ホッカイドウ, 011002,北海道,札幌市,ホッカイドウ,サッポロシ 012025,北海道,函館市,ホッカイドウ,ハコダテシ 012033,北海道,小樽市,ホッカイドウ,オタルシ 012041,北海道,旭川市,ホッカイドウ,アサヒカワシ ...
パーティションテーブルを親子で関連付けして表示する
SQLはこんな感じです。pg_inheritsで親子を繋げます。
select pg_par.relname parent_table_name ,pg_chi.relname child_table_name from pg_inherits inh inner join pg_class pg_par on inh.inhparent = pg_par.oid inner join pg_class pg_chi on inh.inhrelid = pg_chi.oid order by parent_table_name ,child_table_name ;
実行例はこんな感じです。
parent_table_name | child_table_name
--------------------------------+-----------------------------------
zenkoku_chiho_kokyodantai_code | zenkoku_chiho_kokyodantai_code_1
zenkoku_chiho_kokyodantai_code | zenkoku_chiho_kokyodantai_code_10
zenkoku_chiho_kokyodantai_code | zenkoku_chiho_kokyodantai_code_11
zenkoku_chiho_kokyodantai_code | zenkoku_chiho_kokyodantai_code_12
...
親子テーブルを関連付けしてパーティションテーブルの行数とサイズを取得する
select /* 親テーブルは aテーブル(関連付けテーブル)に存在しない。グループ化するために親テーブル名を代入 */ coalesce(a.parent_table_name,pg_class.relname) parent_table_name ,sum(pg_class.reltuples) total_row_num ,sum((pg_class.relpages * 8192)) bytes from pg_class left outer join (select pg_par.relname parent_table_name ,pg_chi.relname child_table_name from pg_inherits inh inner join pg_class pg_par on inh.inhparent = pg_par.oid inner join pg_class pg_chi on inh.inhrelid = pg_chi.oid order by parent_table_name ,child_table_name ) a on pg_class.relname = a.child_table_name where pg_class.relname like 'zenkoku%' group by coalesce(a.parent_table_name,pg_class.relname) ;
実行例
parent_table_name | total_row_num | bytes --------------------------------+---------------+-------- zenkoku_chiho_kokyodantai_code | 1788 | 401408