postgresqlのCONFLICT（UPSERT）のRETURNING句で更新前の値を取りたい

Question

前提・実現したいこと

更新前の値を取りたい

発生している問題・エラーメッセージ

postgresqlのCONFLICT（UPSERT）でテーブルを更新したのち、
更新前の値をRETURNING句で取りたく、以下のようにしましたがこのサブクエリが
複数行を返すようで、エラーが出てしまいます

INSERT INTO
            test VALUES ('001','hoge1', 'hage1'),('002','hoge2', 'hage2'),('003','hoge3', 'hage3')
            ON CONFLICT ON CONSTRAINT testsss DO UPDATE SET
                code           = EXCLUDED.code,
                hoge           = EXCLUDED.hoge,
                hage           = EXCLUDED.hage,
            RETURNING
                id,
　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　hage,
　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　COALESCE((SELECT hoage FROM test WHERE id = id), '') AS before_hoge
        

 ERROR:  more than one row returned by a subquery used as an expression at

例えば以下のようにid決め打ち（111）で行うと、正しく表示されます

INSERT INTO
            test {$column} VALUES {$holder}
            ON CONFLICT ON CONSTRAINT testsss DO UPDATE SET
                code           = EXCLUDED.code,
                hoge               = EXCLUDED.hoge,
                hage             = EXCLUDED.hage,
            RETURNING
                id,
                hage,
　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　COALESCE((SELECT hoage FROM test WHERE id = 111), '') AS before_hoge
        

idに別名をつけてみたり色々しましたが正しく取得できません

postgres (PostgreSQL) 13.4

です

Answer 1

更新前の値をRETURNING句で取りたく

そもそもRETURNINGは更新の結果を取得するものです。
更新前なら、更新する前にselectしておけば良いだけですから。

また、RETURNINGは更新対象となった結果を複数行で返却するので、更新対象テーブルのデータを取得するなら、サブクエリーは不要です。

どうしてもRETURNINGで取得したいなら、更新前の項目をテーブルに追加し、DO UPDATE SETでその項目を更新するようにすれば良いでしょう。

Comments

[ms5025]

回答ありがとうございます。
このパターンだと例えば100件のUPSERTを行う場合、
いくつかはinsertでいくつかはupdateです。
もちろん、更新のなかった行もあります。

なので事前に取っておいて判定することなく、更新対象のあったRETURNINGで取りたいです。
上記
COALESCE((SELECT hoage FROM test WHERE id = id), '') AS before_hoge
について、どこに間違いがあるか教えていただけないでしょうか？
欲しいデータは
更新対象となった複数のid と、その更新前hogeと、更新後のhageです。
事前に取っておくやり方以外、returnでとる方法はありますでしょうか？
（テーブルに項目追加以外で）

[sazi]

サブクエリーで取得できた値は、更新前の情報でしたか？

[ms5025]

そうです！

[sazi]

相関指定できれば良さそうですので、以下の様にしてみてください。
COALESCE((SELECT hoage FROM test t WHERE t.id = id), '') AS before_hoge

[sazi]

> いくつかはinsertでいくつかはupdateです。もちろん、更新のなかった行もあります。なので
対象とするデータは値を指定するんですから、事前にselectして取得できたものが更新対象だと判断できます。

[ms5025]

相関指定しましたが、できないんです・・
全く同じエラーです
ERROR: more than one row returned by a subquery used as an expression at

[sazi]

returningに名称が付けられれば良いんですけど、そういった指定は無いようですから、insert into selectに変更してそこで、更新前値を項目にすれば、returnningで取得できるかもしれません。
多分returnningの対象はinsertに指定しているテーブルが対象だと思うので駄目な気もします。

[sazi]

values内でサブクエリーの指定が出来ればいいんですが、項目が合わなくなりますので。

[sazi]

testsssの内容はどのようなものでしょう？
idをvaluesの内容に含められるなら、with式を多段的に使えば可能かもしれません。

[ms5025]

testsssの内容はcodeとhageでユニークみたいな感じです

[sazi]

適当なコメントでは適当にしか回答出来ませんよ。
リファレンスを見ると、
> RETURNING句を使用するには、RETURNINGで使用するすべての列に対するSELECT権限が必要です。 queryを使用して問い合わせ結果を元に行を挿入する場合は当然ながら、その問い合わせ内で使われる全てのテーブルまたは列に対してSELECT権限を持っている必要があります。

とありますので、insert into selectでreturnningで更新前値は取得出来そうですね。

[ms5025]

適当にしてるつもりはないのですが。
実テーブルの列名を書くわけにはいかないですし
実際の列数はかなりあります。
よくある、ユニーク制約としか言えないです。
質問のテーブルはあくまで一例です。

[ms5025]

すみません、
insert into selectの意味がよくわかりません。このselectは何のselectでしょうか。

上記SQLはtestテーブルに
('001','hoge1', 'hage1'),('002','hoge2', 'hage2'),('003','hoge3', 'hage3')
といった　配列データ（これは非テーブルで配列群です）
をtestsss 制約の元になければinsert あればupdateする
という認識なのですが合ってますでしょうか？

[sazi]

> 実テーブルの列名を書くわけにはいかないですし
それは良いんですが、質問がそれを整合性を付けて編集できていません。
valuseの項目がテーブルの何の項目に対比されているか分かりませんし、hoageとIDとかいきなりで出てきて、 CONFLICT使用しているのに、制約の内容も記載がありませんよね
推測するしかありませんので、回答も的外れになるかもしれないと言っています。
せめて仮のテーブルの定義位は質問に追記して下さい。

[sazi]

> insert into selectの意味がよくわかりません。このselectは何のselectでしょうか。

例示したSQLで回答しようとしたけど、上記理由で回答していません。

[dameo]

一応docker-composeのあるlinux環境でdocker権限を持つユーザーが実行可能なshell scriptを書いてみました。

#!/bin/sh
cat >docker-compose.yml <<EOF
version: '3'
services:
　db:
　　image: postgres:13.4
　　volumes:
　　　- ./data:/var/lib/postgresql/data
　　environment:
　　　POSTGRES_PASSWORD: example
　　ports:
　　　- 5432:5432
EOF
docker-compose up -d
sleep 5 # 適度に待つ
docker-compose exec -T db bash -c "psql --user postgres" <<EOF
\set AUTOCOMMIT off
create table test(code varchar(100) primary key, hoge varchar(100));
insert into test values ('001', 'hoge1'),('002', 'hoge2');
commit;
insert into test
　　　　values ('001', 'hage1'),('002', 'hage2'),('003','hage3')
　　　　on conflict (code) do update set
　　　　　　　　hoge = excluded.hoge
　　　　returning
　　　　　　　　code,
　　　　　　　　(select old_test.hoge from test old_test where old_test.code = test.code)
;
commit;
EOF
docker-compose down

※全角空白2個で半角空白2個を表現しています。

私の環境では以下のような実行結果になりました。

Creating network "tmp_default" with the default driver
Creating tmp_db_1 ... done
CREATE TABLE
INSERT 0 2
COMMIT
code | hoge
------+-------
001 | hoge1
002 | hoge2
003 |
(3 rows)

INSERT 0 3
COMMIT
Stopping tmp_db_1 ... done
Removing tmp_db_1 ... done
Removing network tmp_default

※tmpという名前はスクリプト実行時のカレントディレクトリがtmpなだけです

上記では更新前の値を取れているようですが、確実にそうなるものなのか、判断できないクエリですね。
ご参考までに。横から失礼しました。