postgreSQLでSELECT/UPSERT

TL;DR

INSERT INTO テーブル名(カラム名)
SELECT カラム名 FROM テーブル名
ON CONFLICT ON CONSTRAINT 制約名
DO UPDATE SET
    カラム名 = excluded.カラム名

使用例

使用するテーブルイメージ

• 合格者テーブル（pass_table）

name	last_score
田中	30
山田	20

※nameカラムが主キー

• 受験者テーブル（exam_table）

name	score	country
田中	60	東京
鈴木	40	東京
山田	70	神奈川
木村	60	神奈川

今回は出身都道府県の差別で東京出身の受験者を問答無用で合格とします。
過去に合格実績がある人はそのまま合格者として残ります。

• UPSERT後、合格者テーブル（pass_table）

name	last_score
田中	60
山田	20
鈴木	40

UPSERT相当文

INSERT INTO 
    pass_table(name, last_score)
SELECT 
    name, score
FROM
    exam_table
WHERE
    country = '東京'
ON CONFLICT ON CONSTRAINT pass_table_pkey
DO UPDATE SET
    last_score = excluded.last_score;

処理の流れ

➀まずINSERTするための情報をSELECTします。
SELECT結果は以下のようなイメージになります。

name	score
田中	60
鈴木	40

➁SELECT結果を合格者テーブルにINSERTしようとします。

name	last_score
田中	30
山田	20

↑　INSERT

name	score
田中	60
鈴木	40

このままだと「田中」がかぶってしまってエラーになります。

➂重複時の処理を記述します。
ON CONFLICT ON CONSTRAINT pass_table_pkey
「テーブルの主キーが重複してしまった場合～」

DO UPDATE SET
「以下の値でテーブルを更新します。」

※重複した場合にUPDATEせずに何もしたくない場合はDO UPDATE SETをDO NOTHINGにして、クエリをしめます。

➃更新の値を記述します。
last_score = excluded.last_score;
値が重複した場合、INSERTしようとした値はexcludedという一時的なテーブルのような領域に除外されます。
ですので、そこから値を引っ張ってきてUPDATEしてやります。

以下のような架空のexcludedテーブルがあると考えるとイメージしやすいと思います。

name	last_score
田中	60

注意点としては、INSERT元のカラム名(score)ではなく、INSERT先のカラム名(last_score)を指定してやることです。

以上