【非PDO】PHP+PostgreSQLで日付型をINSERT→UPDATEする（prepared&bind）

やたら苦戦してしまったのでメモに残します。

やりたいこと

• 変数$calc_dateには""もしくは日付YYYY-MM-DDを表す文字列が入る。
• この変数をdate型のカラムにアップサートしたい。（INSERTして重複する場合はUPDATEしたい。）
• SQL文はプリペアードしたものにバインドしたい。
• pg_prepare, pg_executeを使用する。（PDOを使用しない）
• 環境：PHP7、PostgreSQL12

実装

// DB接続処理
$link = pg_connect("host=host_name port=XXXX dbname=db_name user=user_name password=XXXX");

// 日付もしくは""の配列
$date_list = ["", "2021-07-13", "", "2020-09-01"];

// テーブルINSERT（重複したらUPDATE）のSQL
$sql = 'INSERT INTO table_name (id, sample_date)
            VALUES ($1, $2)
        ON CONFLICT (id)
        DO UPDATE SET  sample_date = $3
        ';

// プリペアードステートメント作成（preparedする）
$res = pg_prepare(
            $link,
            'pr_table_name01', // ←これはプリペアードステートメントの名前
            $sql
        );

$cnt = 0;
foreach($date_list as $row){
    $cnt = $cnt + 1;
    // 日付もしくは""が入る
    $calc_date = $row;

    // ""だったらnull、それ以外は''（シングルクオート）で囲う
    $calc_date = $calc_date == "" ? null : '\''.$calc_date.'\'';

    // プリペアードステートメント実行（bindする）
    $res = pg_execute(
               $link,
               'pr_table_name01',
               [$cnt, $calc_date, $calc_date]
           );
}
//DB後処理
$close_flag = pg_close($link);

キーポイント

• PostgreSQLのdate型には null か日付しか入らない。""（空文字）は不可。
• ON CONFLICT文はPostgreSQL独自の構文。MySQLではON DUPLICATE KEYを使う。
• 日付形式の文字列をバインドする時は''で囲う。（nullは囲んじゃダメ！）

参考：ループ内でプリペアードステートメントを作成

foreach内でpreparedしたい場合は、最初の1回のみ行うようにする。

// DB接続処理
$link = pg_connect("host=host_name port=XXXX dbname=db_name user=user_name password=XXXX");

// 日付もしくは""の配列
$date_list = ["", "2021-07-13", "", "2020-09-01"];
$cnt = 0;
foreach($date_list as $row){
    $cnt = $cnt + 1;
    // 日付もしくは""が入る
    $calc_date = $row;

    // ""だったらnull、それ以外は''（シングルクオート）で囲う
    $calc_date = $calc_date == "" ? null : '\''.$calc_date.'\'';

    // テーブルINSERT（重複したらUPDATE）のSQL
    $sql = 'INSERT INTO table_name (id, sample_date)
                VALUES ($1, $2)
            ON CONFLICT (id)
            DO UPDATE SET  sample_date = $3
           ';

    // 1回目のみプリペアードステートメント作成（preparedする）
    if($cnt == 1) {
       $res = pg_prepare(
                  $link,
                  'pr_table_name01', // ←これはプリペアードステートメントの名前
                  $sql
              );
    }
    // プリペアードステートメント実行（bindする）
    $res = pg_execute(
               $link,
               'pr_table_name01',
               [$cnt, $calc_date, $calc_date]
           );
}
//DB後処理
$close_flag = pg_close($link);

今どきはPDOが主流なので（プロジェクトの方針で禁止されない限り）PDO形式が良いと思います。