📑

DBのデータをクエリで増やす方法

2023/11/04に公開

PostgreSQL

SQL

tech

はじめに

テストでデータを一時的に増やして確認したいことがあったので、手っ取り早く増やす方法を試してみました。

generate_series関数の話題ではないです。記事を書いてから知りました・・・

前提として

DBはpostgresql
targetsというテーブルのデータを増やしたい
特定のユーザのデータを増やしたい。targetsのカラムuser_idで指定

`targets`のテーブル構造

CREATE TABLE targets(
    id character varying(36) NOT NULL,
    parent_id character varying(36) NOT NULL,
    user_id character varying(36) NOT NULL,
    created_at timestamp without time zone NOT NULL,
    updated_at timestamp without time zone NOT NULL,
    PRIMARY KEY(id)
);

クエリ

INSERT INTO targets
SELECT CONCAT(EXTRACT(EPOCH FROM NOW()), ROW_NUMBER() OVER(ORDER BY id)), parent_id, user_id, created_at, updated_at
FROM targets
WHERE user_id = 'B3Q5P7D72H1HBQBA42VEGCS650';

解説

SELECTで対象となったデータを複製し、idを置き換えて登録しています。
複製の元になるデータが必要なので、最低1件だけはINSERT文やアプリケーションの操作などでで作成する必要があります。
最初1件だったとしても、1回クエリを実行すると、2件に増えます。
クエリをもう一度実行すると、4件になり、
3回目で8件、4回目で16件と倍々でデータが増やせます。

次に、クエリの構文について解説します。
まずINSERT文にSELECT文をくっつけて、SELECTした対象をINSERTします。
※自分はINSERT-SELECTって言ってましたが、正式な言い方がわからないです。

SELECT句

SELECT句はINSERTする側のテーブルに項目を合わせる必要があります。
今回は同じテーブルなので、SELECT *でもいいのですが、
テーブルの制約上idの重複を許さないので、一意性制約（変換できない）に違反しSQLエラーが発生します。
そのための工夫として、idのカラムに実行のたびに値が変わる情報を指定しています。

EXTRACT(EPOCH FROM NOW())
エポック秒を取得します。
1970年1月1日午前0時0分0秒から現在までの秒数が取れます。
真ん中の.は小数点なので、マイクロ秒あたりまで取れてそうです。

SELECT EXTRACT(EPOCH FROM NOW());

      extract      
-------------------
 1698716974.196163
(1 row)

ROW_NUMBER() OVER(ORDER BY id)
行番号。
SELECTした際の行番号が取得できます。

SELECT ROW_NUMBER() OVER(ORDER BY id) AS rownum FROM targets;

 rownum 
--------
      1
      2
      3
      4
      5
      ・・・

この2つをCONCATで繋げてidの代わりにしています。

単純にランダムな値であれば、random関数もあるのですが、エポック秒にすることで、並べ替えしやすかったりと私にとって都合が良い面があります。

続く他のカラムはSELECTで取得した項目をそのまま使用しています。

もし他の値も変えたい場合は、id同様に別な値に置き換えることでINSERTされるデータを変化させられます。
例えば以下のようにすることで、user_idはSELECTした際の行数、created_at はシステム日付、upadted_atはNULLみたいなこともできます。

INSERT INTO targets
SELECT CONCAT(EXTRACT(EPOCH FROM NOW()), ROW_NUMBER() OVER(ORDER BY id)), parent_id, ROW_NUMBER() OVER(ORDER BY id), CURRENT_TIMESTAMP, NULL
FROM `targets`
WHERE user_id = 'B3Q5P7D72H1HBQBA42VEGCS650';

※元のカラム以外の値を指定する場合、型に合った値を設定しないとクエリ実行時にエラーになる点は注意が必要です。
※ちなみに今回のテーブル構造では、updated_atがNOT　NULLなのでNULLは指定できないのでクエリ実行時にエラーになってしまいます。

INSERT-SELECTについて軽く

INSERT文のVALUESに該当する箇所にSELECTの結果が設定されてくるイメージです。
SELECT句はINSERTするテーブルのカラムに合わせる必要があります。
※今回はINSERTとSELECTで同じテーブルを指定していますが、別のテーブルでも指定が可能です。

さいごに

SELECT-INSERTとエポック秒、行番号を組み合わせて既存データを増殖する方法について解説しました。
冒頭にも書きましたが、実はgenerate_series関数というめちゃ便利な関数が標準で備わっているので、
連番や連続する日付などをデータで作りたい場合に重宝しそうです。

generate_series関数の詳しい解説はこちらが参考になります。

2013年の記事でしたが、generate_series関数やその他クエリについていくつか試してみましたが、
私の確認した環境PostgreSQL 14.5でも動作しています。

PostgreSQL公式情報

連続値生成関数としてgenerate_seriesが紹介されています。