ISERT SELECT の注意点！ 正常終了してるのにデータが入らない！？ ~改善案を添えて~

はじめに

今回仕事でpgsqlを扱っており、APIでぱっと見想定通り動作するであろうINSERTのSQL文作成して実行したところ、正常終了しているのにも関わらず登録されないという悲しい話がありましたので、メモ兼アウトプットがてら書いていきたいと思います。
既存のコードを改修していった結果、本来こんな書き方しないだろうというものになったのですが、そのおかげで新たな知見を得ることができました。

やりたかったこと

やりたかったこととしては、とある空のテーブルにINSERT SELECTを使ってデータを登録しようとしました。参照元のテーブルはINSERTするテーブルです。
今回使うテーブルを以下のように定義します

table.sql

CREATE TABLE employees (
    employee_id INTEGER PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50),
    email VARCHAR(100)
);

間違えたSQL文

元々SQL文でemployee_id部分を自動生成をしていたのですが、とある事情によりコードで生成した値を固定値として代入することになり、単純に修正したものです。

insert_sql.sql

INSERT INTO employees (
    employee_id,
    first_name,
    last_name,
    email
)
SELECT
    1 as employee_id,
    John as first_name,
    Doe as last_name,
    [e-mail] as email
FROM
    employees

このSQL文の実行結果としては、正常終了して何も登録しないという形になります。
固定値入れてるのに何でデータが登録されないんだ！！エラーとかもはいてないし、何も問題ないはず！！

原因

INSERT SELECTするとき 参照元のテーブルには少なくとも1行が何かしらのデータがないといけない らしいです。当たり前の話ですが参照するデータがないとSELECT文は何も返せません。SELECT君が何も返さないとINSERT君も動いてくれないらしく、仮に固定値のように入れてもSELECT君がなにも返さないのでINSERT君も動かないという現象が起きたみたいですね。
じゃあ適当なデータがあれば動くのかというとその通りでプライマリーキー制約に従うようにデータを1つ登録した後に上記のSQL文を動かすと正常終了して登録されていました。

修正

解決方法はいくつかありますが、前提として空のテーブルにデータを登録するSQLを書いていきます。

案1：素直にINSERT VALUES

INSERT VALUESで固定値を登録していく方法です。コード上で値をSQL文に代入していって実行していったりしました。

insert_values.sql

INSERT INTO employees (
   employee_id,
   first_name,
   last_name,
   email
)
VALUES(
   1,
   John,
   Doe,
   [e-mail]
)

これなら特に問題なく登録されてます。普通に登録するならこれでいいです。というか固定値で登録するならこれでいいです()

案2：シーケンスを使う

シーケンスを使って値を固定で段階的に登録していきます。

insert_coalesce.sql

INSERT INTO employees (
    employee_id,
    first_name,
    last_name,
    email
)
SELECT
    nextval(employee_id_seq) as employee_id,
    John as first_name,
    Doe as last_name,
    [e-mail] as email
FROM
    employees

シーケンスで値を登録しているので1，2，3…といった感じで階段的に登録していきます。

案3：COALESCEを使う

まずCOALECEとはなにかといいますと、簡単にいうと限定的な条件文みたいなものです。限定的というのはNULLかNULL以外かの処理しかいてくれません。ただこれはSQL文で値を設定してくれるので任意のルールの値で登録することができます。

insert_coalesce.sql

INSERT INTO employees (
    employee_id,
    first_name,
    last_name,
    email
)
SELECT
    COALESCE(MAX(employee_id) + 1, 1) as employee_id,
    John as first_name,
    Doe as last_name,
    [e-mail] as email
FROM
    employees

このSQL文では参照元のemployeesにデータがあればemployee_idの最大値をとってその値に1を足した値を設定し、データがないときは”1”を入れてくれます。この分なら独自のルールで値を設定していくことができます。

最後に

他にもIF文とかCASE文とかありますが正直全部書く体力はないので割愛します(許してください)
単純なVALUESを使ったやり方からCOALESCEのような関数を使ったやり方まで色々あり、用途ごとでかなり使いわけができることを知れてよかったです。

今回の感想としてはINSERT文で正常終了したのにデータが存在しないことがあるのにびっくりしました…(今までは制約によるエラーとか文字制限によるエラーとかで何かとすぐ解決できる系でした)
てか初めから修正するときにめんどくさがらず確実に動く文で書くべきだったなぁって思います。

余談ですが、今回初めての投稿なので拙い部分が多かったと思いますが、プログラミング能力だけでなく伝え方とかドキュメントの残し方の能力も上げていきたいなと思います。

今回の記事が皆さんのお役に立てましたら幸いです。またのご訪問をお待ちしております！