C#でNpgsqlを使ってPostgreSQLへアクセス【RANGEパーティションのあるテーブル作成】

はじめに

今回は範囲を指定したパーティションテーブルの作成を行います。

範囲といえば蓄積した値ごとや年ごとの値に振り分けるなどがあります。

年ごとに関しては別の手法も併せて紹介したいと思います。

Npgsqlの本家情報は

私の作成するサンプルソースファイルは

テーブルは前回と同じ下記構成となります。

テーブル名	型	概要
time	timestamp	トランザクション開始時刻または入力された日付
name	text	任意の文字列
numeric	integer	任意の数値

予め決められた値でパーティションテーブルを作成します。

基となるテーブル

最初の例として値の範囲を設定したパーティションテーブルを作成してみます。

using NpgsqlConnection con = new("Server=127.0.0.1; Port=5432; User Id=test_user; Password=pass; Database=db_PostgreTest; SearchPath=public");
con.Open();
using NpgsqlCommand cmd = new(@"CREATE TABLE data(time timestamp DEFAULT clock_timestamp(), name text, numeric integer) PARTITION BY RANGE (numeric)", con);

上記CREATE TABLEの後にある「PARTITION BY RANGE(numeric)」がカラム名「numeric」がレンジパーティションテーブルの振り分けを決める要素である事を示します。

文法としては前回のリストパーティションと同じ文法ですね。「LIST」と「RANGE」が違うだけです。

これも前回同様に存在していればCREATE TABLEを実行しない「IF NOT EXISTS」も使えます。

パーティションテーブル

次に指定されたデータのレンジごとに振り分けるパーティションテーブルを作成します。

using NpgsqlConnection con = new("Server=127.0.0.1; Port=5432; User Id=test_user; Password=pass; Database=db_PostgreTest; SearchPath=public");
con.Open();
using NpgsqlCommand cmd = new(@$"CREATE TABLE {PartitionNname}_1 PARTITION OF data FOR VALUES FROM (1) TO (6);", con);
_ = cmd.ExecuteNonQuery();
cmd.CommandText = @$"CREATE TABLE {PartitionNname}_2 PARTITION OF data FOR VALUES FROM (6) TO (11);";
_ = cmd.ExecuteNonQuery();

上記３行目と6行目がレンジパーティションテーブル作成のクエリ文です。

注意しなければいけないのは例えば３行目のクエリ文の場合範囲を示す(1)と(6)です。

(1)は含まれますが(6)は含まれません。

式で表すと「下限値 <= データ < 上限値」となります。

つまり3行目で作成したパーティションテーブルに入る値は1～5となり6行目で作成したパーティションテーブルに入る値は6～10となります。

ちなみに範囲が重なる場合はテーブル作成時に例外が発生します。

データをINSERETするとどのようになるか確認してみます。

cmd.CommandText = @"INSERT INTO data(name, numeric) VALUES ('a', 1);";
var result1 = cmd.ExecuteNonQuery();
cmd.CommandText = @"INSERT INTO data(name, numeric) VALUES ('b', 10);";
var result2 = cmd.ExecuteNonQuery();

「numeric」が「1」と「10」のデータです。

パーティションテーブルは1～5が「range_numeric_1」、6～10が「range_numeric_2」のパーティションです。

確認は簡単にできるpgAdmin4を使用します。

最上位のテーブル

最上位のテーブルで条件なしのSELECTをすればnumericが1でも10でも取得できるのが分かります。

numericが1～5の条件で振り分けられるパーティションテーブル

numericが1のときはテーブル名「range_numeric_1」に振り分けるよう指定してあり、このテーブルでSELECTするとnumericが1のデータだけが入っているのが分かります。

numericが6～10の条件で振り分けられるパーティションテーブル

numericが10のときはテーブル名「range_numeric_2」に振り分けるよう指定してあり、このテーブルでSELECTするとnumericが10のデータだけが入っているのが分かります。

年ごとのリストパーティションテーブル

年ごとの範囲なら上記を参考にするとtimestamp型で範囲指定FOR VALUES FROM ('2020-1-1 0:0:0') TO ('2021-1-1 0:0:0')で2020年の範囲指定となるので年ごとのパーティションテーブルを作成できますが、リストパーティションテーブルでも年ごとのパーティションテーブルは作成可能です。

書き方は

CREATE TABLE data(time timestamp DEFAULT clock_timestamp(), name text, numeric integer) PARTITION BY LIST (date_part('year', time))

PARTITION BY LISTまでは前回の記事と同じですが、その後に指定するテーブル名の「(date_part('year', time))」が重要になります。

date_partはPostgreSQLの日付/時刻関数で日付のいち部分を取得します。

つまり今回の場合はテーブル名「time」の年('year')を取得し、その値がLISTパーティションの振り分け値となります。

そしてリストパーティションテーブルの作成は

CREATE TABLE _2020 PARTITION OF data FOR VALUES IN (2020);

と、前回解説したリストパーティションテーブルと同じ記述になります。

年ごとのパーティションテーブルを作成する場合、こちらの方がシンプルなのでC#のプログラムでクエリ文字列を作る場合でも簡単かと思います。