大量データをPostgreSQLのテーブルに投入する

はじめに

お疲れ様です。
@いけふくろうです。

イニシャルのデータとして設定テーブルに投入の必要があるケースがあるかと思います。
数十件であれば、手動でのDML文作成コストは微々たるものなので、気になりませんが、件数が多くなるとつらくなります。

Excelなどに貼り付けて、関数でINSERT文を作っても良いですが、そのモチベーションがない時もありますのでそんな時の方法です。

どうやって

ストアドプロシージャを作り、テーブルに投入して、投入されたデータをSQLクライアントツールなどからINSERT文をエクスポートする方針にします。

私は、PostgreSQLでストアドを使ったことがないので、こんな時には、ChatGPT-4さんに対応いただきました。

コード

要件

• 販売可能地域を管理するテーブルを定義したので、管理レコードを投入したい
• 郵便番号リストをインプットにして、PostgreSQLのテーブルへ投入するストアドプロシージャを作成する

リスト
101-0064
101-0061
100-0000
101-8372
101-8449
101-8385
101-8383
101-8381
101-8375
101-8405
101-8371
101-8370
101-8366
101-8365
101-8421
101-8672
101-8378
101-8354
101-8347
101-8346

投入するテーブル

CREATE TABLE IF NOT EXISTS deliverable_areas
(
    id              SERIAL                                PRIMARY KEY,
    product_id      INT                                   NOT NULL,
    prefecture_id   INT                                   NULL,
    postal_code     VARCHAR(8)                            NULL,
    updated_at      TIMESTAMP DEFAULT (CURRENT_TIMESTAMP) NOT NULL,
    created_at      TIMESTAMP DEFAULT (CURRENT_TIMESTAMP) NOT NULL,
    CONSTRAINT fk_deliverableareas_01 FOREIGN KEY (product_id) REFERENCES product(id) ON DELETE RESTRICT ON UPDATE RESTRICT,
    CONSTRAINT fk_deliverableareas_02 FOREIGN KEY (prefecture_id) REFERENCES prefecture(id) ON DELETE RESTRICT ON UPDATE RESTRICT
);

INSERT内容

• id: 1から連番
• product_id: 81
• prefecture_id: null
• postal_code: リストの郵便番号

ストアドプロシージャ
コード

CREATE OR REPLACE PROCEDURE insert_deliverable_areas()
LANGUAGE plpgsql
AS $$
DECLARE
    -- Define an array of postal codes
    postal_codes text[] := ARRAY[
        '101-0064',
        '101-0061',
        '100-0000',
        '101-8372',
        '101-8449',
        '101-8385',
        '101-8383',
        '101-8381',
        '101-8375',
        '101-8405',
        '101-8371',
        '101-8370',
        '101-8366',
        '101-8365',
        '101-8421',
        '101-8672',
        '101-8378',
        '101-8354',
        '101-8347',
        '101-8346'
    ];
    product_id int := 81;
    prefecture_id int := NULL;
    i int := 1;
    postal_code text;
BEGIN
    FOREACH postal_code IN ARRAY postal_codes
    LOOP
        INSERT INTO deliverable_areas (id, product_id, prefecture_id, postal_code) 
        VALUES (i, product_id, prefecture_id, postal_code);
        i := i + 1;
    END LOOP;
END; $$

実行方法

CALL insert_deliverable_areas();

以上です。
本記事が何かの一助になれば幸いです。