PostgreSQL - 1つのSQLでマスターに追加してテーブルで利用する

前提のおはなし

• テーブルAとテーブルBは両方にidがあります
• テーブルBにはテーブルA_idフィールドで関連付けをしています
• これらはFOREIGN KEYで外部キー制約をかけています

問題

• この2つのテーブルに、新しい行を追加したいので、テーブルAに行を追加、その後テーブルBに行を追加する
• これをなるべく1つのSQLで行いたい

SQLで解決策

WITH ins AS (
    INSERT INTO table_a (カラム1, カラム2, カラム3)
    VALUES (@カラム1, @カラム2, @カラム3)
    ON CONFLICT (カラム1) DO NOTHING
    RETURNING id
), selected_id AS (
    SELECT id FROM ins
    UNION ALL
    SELECT id FROM table_a WHERE カラム1 = @カラム1
    LIMIT 1
)
INSERT INTO table_b (table_a_id, カラムX, カラムY, カラムZ)
SELECT id, @カラムX, @カラムY, @カラムZ FROM selected_id
RETURNING id;

• まず、Insertをかけてみます。CONFLICTで衝突した場合は値が返りません
• 次のSELECTでCONFLICTの原因となった値で検索しています
• その結果の一つを使って、テーブルに値を挿入しています

C#で実装

これを以下のように実装してみました。
個人的には「複数行の文字列を貼り付けただけ」のSQLはとても
気持ちが悪いのですが、今はわかりやすさ優先で記載しています。

実際のコーディング時には、文字列連結でやるか、StringBuilderでやるかと
思います

using System;
using System.Threading.Tasks;
using Npgsql;

class Program
{
    static async Task Main()
    {
        var connString = "Host=localhost;Username=postgres;Password=yourpassword;Database=yourdb";
        await using var conn = new NpgsqlConnection(connString);
        await conn.OpenAsync();

        var column1Value = "example";  // 実際の値に置き換え
        var column2Value = "data";
        var column3Value = "more data";
        var columnXValue = "valX";
        var columnYValue = "valY";
        var columnZValue = "valZ";

        var query = @"
            WITH ins AS (
                INSERT INTO table_a (カラム1, カラム2, カラム3)
                VALUES (@col1, @col2, @col3)
                ON CONFLICT (カラム1) DO NOTHING
                RETURNING id
            ), selected_id AS (
                SELECT id FROM ins
                UNION ALL
                SELECT id FROM table_a WHERE カラム1 = @col1
                LIMIT 1
            )
            INSERT INTO table_b (table_a_id, カラムX, カラムY, カラムZ)
            SELECT id, @colX, @colY, @colZ FROM selected_id
            RETURNING id;
        ";

        await using (var cmd = new NpgsqlCommand(query, conn))
        {
            cmd.Parameters.AddWithValue("col1", column1Value);
            cmd.Parameters.AddWithValue("col2", column2Value);
            cmd.Parameters.AddWithValue("col3", column3Value);
            cmd.Parameters.AddWithValue("colX", columnXValue);
            cmd.Parameters.AddWithValue("colY", columnYValue);
            cmd.Parameters.AddWithValue("colZ", columnZValue);

            var result = await cmd.ExecuteScalarAsync();
            if (result == null)
            {
                throw new Exception("テーブルBの挿入に失敗しました");
            }

            Console.WriteLine($"テーブルBに挿入したID: {result}");
        }
    }
}