PostgreSQLでテスト用データを作る方法

PostgreSQL

検証用として利用できるデータベース、レコードを作成する方法を、先人たちの知恵を元に記載していきます。

環境情報

サンプルデータベースの作成

まるっとサンプルとなるデータベースを作成する方法です。

以下のEducation用サイトで用意してくれているものを利用できます。DVDレンタル屋さんを例にしたデータベースとなっています。

POSTGRESQL TUTORIAL - Sample Database

Objects

  • 15 tables
  • 1 trigger
  • 7 views
  • 8 functions
  • 1 domain
  • 13 sequences

ER Model

ER図のPDF

tables

  • actor
    • stores actors data including first name and last name.
  • film
    • stores films data such as title, release year, length, rating, etc.
  • film_actor
    • stores the relationships between films and actors.
  • category
    • stores film’s categories data.
  • film_category
    • stores the relationships between films and categories.
  • store
    • contains the store data including manager staff and address.
  • inventory
    • stores inventory data.
  • rental
    • stores rental data.
  • payment
    • stores customer’s payments.
  • staff
    • stores staff data.
  • customer
    • stores customers data.
  • address
    • stores address data for staff and customers
  • city
    • stores the city names.
  • country
    • stores the country names.

Postgreへのロード方法は、指定のzipファイルをダウンロードして、解凍してから、pg_restoreするだけです。

wgetして、unzipして、

$ wget http://www.postgresqltutorial.com/wp-content/uploads/2017/10/dvdrental.zip
$ unzip dvdrental.zip

ダウンロードURLはこちら。

POSTGRESQL TUTORIAL - Load Sample Database

リストア用のデータベースを作成して、

postgres=# create database dvdrental;
CREATE DATABASE

リストアします。

$ pg_restore -h localhost -p 5432 -U postgres -d dvdrental dvdrental.tar

PostgreSQL - pg_restore

サンプルとなるデータベースが作成されています。

dvdrental=# \dt
                リレーション一覧
 スキーマ |     名前      |    型    |  所有者
----------+---------------+----------+----------
 public   | actor         | テーブル | postgres
 public   | address       | テーブル | postgres
 public   | category      | テーブル | postgres
 public   | city          | テーブル | postgres
 public   | country       | テーブル | postgres
 public   | customer      | テーブル | postgres
 public   | film          | テーブル | postgres
 public   | film_actor    | テーブル | postgres
 public   | film_category | テーブル | postgres
 public   | inventory     | テーブル | postgres
 public   | language      | テーブル | postgres
 public   | payment       | テーブル | postgres
 public   | rental        | テーブル | postgres
 public   | staff         | テーブル | postgres
 public   | store         | テーブル | postgres
(15 行)

dvdrental=# \d actor
                                            テーブル "public.actor"
     列      |             型              | 照合順序 | Null 値を許容 |               デフォルト
-------------+-----------------------------+----------+---------------+-----------------------------------------
 actor_id    | integer                     |          | not null      | nextval('actor_actor_id_seq'::regclass)
 first_name  | character varying(45)       |          | not null      |
 last_name   | character varying(45)       |          | not null      |
 last_update | timestamp without time zone |          | not null      | now()
インデックス:
    "actor_pkey" PRIMARY KEY, btree (actor_id)
    "idx_actor_last_name" btree (last_name)
参照元:
    TABLE "film_actor" CONSTRAINT "film_actor_actor_id_fkey" FOREIGN KEY (actor_id) REFERENCES actor(actor_id) ON UPDATE CASCADE ON DELETE RESTRICT
トリガー:
    last_updated BEFORE UPDATE ON actor FOR EACH ROW EXECUTE PROCEDURE last_updated()

レコードを作成する

次は、ランダムな値をもった、大量のレコードを作成する方法です。これには、以下の Let's Postgres の記事を参考にしています。

Let's Postgres - PostgreSQLでテストデータを作成する

serialな整数の生成

> create table serial (code int);
>
> insert into serial (code)
    select i from generate_series(1, 10) as i;
>
> select * from serial;
 code
------
    1
    2
    3
    4
    5
    6
    7
    8
    9
   10
(10 行)

generate_series とは、ポスグレで用意されている 連続値生成関数 とのこと。関数に与えられた引数により、初期値・終了値・ステップを設定して、連続したint型やnumeric型を返してくれます。

PostgreSQL - generate_series

randomな整数の生成

> create table random (code int);
>
> insert into random (code)
    select (random() * 100) from generate_series(1, 10);
>
> select * from random;
 code
------
   44
   84
   45
   89
   83
   52
   71
   46
   24
   27
(10 行)

ポスグレの random 関数は、0から1までの乱数値を返してくれるので、それを利用する方法です。

PostgreSQL - random

randomな文字列の生成

> create table random_string (name varchar(5));
> 
> insert into random_string (name)
    select left(md5(i::text), 5) from generate_series(1, 10) as i;
> 
> select * from random_string;
 name
-------
 c4ca4
 c81e7
 eccbc
 a87ff
 e4da3
 16790
 8f14e
 c9f0f
 45c48
 d3d94
(10 行)

md5 関数は、string型の引数を貰って、そのMD5ハッシュ値を返してくれる関数です。generate_seriesで作成されている i は整数値なので、md5関数に渡す際にstring型のtext型に変換しています。 ::text とは、text型へのcast処理となっています。

PostgreSQL - md5

型キャストは、あるデータ型から他のデータ型への変換を指定します。 PostgreSQLは型キャストに2つの等価な構文を受け付けます。

CAST ( expression AS type )
expression::type

CAST構文はSQLに準拠したものです。 ::を使用する構文は、PostgreSQLで伝統的に使用されている方法です。

PostgreSQL - 評価式

randomな長い文字列の生成

上の例では、md5のハッシュ値は128ビットなので、最大で32文字までの文字列しか生成できません。その回避策。

> create table random_long_string (code int, name varchar);
> 
> insert into random_long_string (code, name)
    select i, string_agg(str, '') from
    (select i, md5(i::text) as str from generate_series(1, 2), generate_series(1, 10) as i) as t
    group by i;
> 
> select * from random_long_string order by code;
 code |                               name
------+------------------------------------------------------------------
    1 | c4ca4238a0b923820dcc509a6f75849bc4ca4238a0b923820dcc509a6f75849b
    2 | c81e728d9d4c2f636f067f89cc14862cc81e728d9d4c2f636f067f89cc14862c
    3 | eccbc87e4b5ce2fe28308fd9f2a7baf3eccbc87e4b5ce2fe28308fd9f2a7baf3
    4 | a87ff679a2f3e71d9181a67b7542122ca87ff679a2f3e71d9181a67b7542122c
    5 | e4da3b7fbbce2345d7772b0674a318d5e4da3b7fbbce2345d7772b0674a318d5
    6 | 1679091c5a880faf6fb5e6087eb1b2dc1679091c5a880faf6fb5e6087eb1b2dc
    7 | 8f14e45fceea167a5a36dedd4bea25438f14e45fceea167a5a36dedd4bea2543
    8 | c9f0f895fb98ab9159f51fd0297e236dc9f0f895fb98ab9159f51fd0297e236d
    9 | 45c48cce2e2d7fbdea1afc51c7c6ad2645c48cce2e2d7fbdea1afc51c7c6ad26
   10 | d3d9446802a44259755d38e6d163e820d3d9446802a44259755d38e6d163e820
(10 行)

「md5ハッシュ値を2回繰り返すテーブル」と「md5ハッシュ値を10回繰り返すテーブル」をCROSS JOINし、i列(10回繰り返し処理時の添字)をgroup by句として、ハッシュ値を連結したものです。

string_agg 関数の書式は下記となっており、expressionで与えられた値を、delimiterを区切り文字として連結してくれるものとなります。

string_agg(expression, delimiter)

PostgreSQL - 集約関数

randomなマルチバイト文字列の生成

> create table random_multi_byte_string (name varchar(3));
> 
> insert into random_multi_byte_string (name)
    select chr(12353 + (random() * 100)::int) ||
    chr(12353 + (random() * 100)::int) ||
    chr(12353 + (random() * 100)::int)
    from generate_series(1, 10);
> 
> select * from random_multi_byte_string;
  name
--------
 ゛ゞゖ
 ぎれて
 ろよご
 やっぼ
 にもむ
 ゐむげ
 ずゟ゚
 めぶご
 ィじゔ
 にちえ
(10 行)

chr 関数は、Unicodeコードポイントとなるint型の引数を貰って、その文字を返してくれる関数です。ひらがなのコードポイントは12354より始まるので、その数字にランダムな数値を加算してあげて、ランダムなマルチバイト文字を生成しているものになります。

PostgreSQL - chr

Unicodeのコードポイント

ランダムなタイムスタンプ型の生成

> create table random_timestamp ( tmsp timestamp );
> 
> insert into random_timestamp (tmsp)
    select (now() + trunc(random() * 100) * '1 hour'::interval) from generate_series(1, 10);
> 
> select * from random_timestamp;
            tmsp
----------------------------
 2019-05-09 21:25:12.119117
 2019-05-10 06:25:12.119117
 2019-05-10 09:25:12.119117
 2019-05-06 22:25:12.119117
 2019-05-07 03:25:12.119117
 2019-05-08 19:25:12.119117
 2019-05-09 16:25:12.119117
 2019-05-07 15:25:12.119117
 2019-05-09 21:25:12.119117
 2019-05-07 19:25:12.119117
(10 行)

now 関数で取得した現在の時刻に対して、ランダムなインターバル（random関数で取得した値）を加算することで、ランダムな日時を生成しています。

レコードを複製して行数を増やす

既存のテーブルをCROSS JOINして、強引にレコード数を増やす方法。

> select code from test;
 code 
------
    1 
    2 
(2 行)
>
> select A.code from test A, test B;
 code 
------
    1 
    1 
    2 
    2 
(4 行)

数回繰り返すだけで、すごい行数になっていくので、これを繰り返しINSERTしてあげる。