【PostgreSQL】 SQLでテスト用データを生成する
generate_series関数による連続値の生成
テスト用のデータをgenerate_series関数を使って連続した整数値、タイムスタンプを生成できる。generete_siries関数はgenerete_siries(start, stop)、または、generete_siries(start, stop, interval)の
形で使用する。引数の型はint, bigint, numeric, timestamp, timestamp with timezoneを取ることができる。
-- 1から10までの数を生成する
# select * from generate_series(1, 10);
generate_series
-----------------
1
2
3
4
5
6
7
8
9
10
-- 1から10までの数を2つおきに生成する
# select * from generate_series(1, 10, 2);
generate_series
-----------------
1
3
5
7
9
-- 2020/1/1から2020/1/10 までの日付をtimestamp with time zone型で生成する。
# select * from generate_series('2020-01-01'::timestamp with time zone, '2020-01-10'::timestamp with time zone, '1 day');
generate_series
------------------------
2020-01-01 00:00:00+00
2020-01-02 00:00:00+00
2020-01-03 00:00:00+00
2020-01-04 00:00:00+00
2020-01-05 00:00:00+00
2020-01-06 00:00:00+00
2020-01-07 00:00:00+00
2020-01-08 00:00:00+00
2020-01-09 00:00:00+00
2020-01-10 00:00:00+00
PostgreSQLでのテストデータ用文字列生成に便利な関数
md5(string)関数で引数で渡された文字列のMD5ハッシュ値を生成できる。また、left(string, int)関数で引数で渡された文字列を先頭からn文字返すことができる。
-- MD5ハッシュ値を生成
# select md5('hoge');
md5
----------------------------------
ea703e7aa1efda0064eaa507d9e8ab7e
(1 row)
-- MD5ハッシュ値の先頭10文字を取得
# select left(md5('hoge'), 10);
left
------------
ea703e7aa1
(1 row)
テーブルへのデータ投入
これらの関数を使ってテーブルへデータを投入する。下記のように顧客データ用テーブルを作成し、データを10000件投入する。
mod関数を使った区分値の生成、ramdom関数を使った一定期間内のtimestam値の生成も行なっている。
-- 顧客データ用テーブル
create table customer (
id int primary key, -- 主キー
tenant_name text, -- マルチテナントを想定したテナント名
customer_name text, -- 顧客名
customer_div int, -- 顧客区分
birth_day timestamp with time zone -- 顧客の生年月日
);
-- データ投入
insert into customer (
id,
tenant_name,
customer_name,
customer_div,
birth_day
)
select
i,
case -- 2つのテナント名を生成
when mod(i, 2) = 0 then 'tenant1'
when mod(i, 2) = 1 then 'tenant2'
end,
left(md5(i::text), 10),
mod(i, 5), -- 顧客区分値として0~4 を生成
timestamp '1970-01-01 00:00:00' + random() * (timestamp '2000-12-31 23:59:59' - timestamp '1970-01-01 00:00:00')
from
generate_series(1, 10000, 1) as i;
-- 件数をカウント
# select count(*) from customer ;
count
-------
10000
(1 row)