スコア
対策期間・時間
対策に使った本など
- 1.AWS認定ソリューションアーキテクト－プロフェッショナル～試験特性から導き出した演習問題と詳細解説～
- 2.AWS WEB問題集で学習しよう
- 3.AWS 認定ソリューションアーキテクトプロフェッショナル模擬試験問題集（全5回分375問）
- 4.AWS関連のWebサイト
- 5.公式模試
感想など
- 合格ライン正答率75%のしんどさ
- 今回は自宅受験を選択

2020-08-31

PostgreSQLでパーティションテーブルを親子で関連付けして表示する

PostgreSQL SQL

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