検索して見つかったらそのID、見つからなかったらInsertした後、そのIDを取得したい。

Question

上記の通りのことをしたいのですがスマートにできないので質問させていただきました。

Python(version3.6)からpsycopg2モジュールを用いてPostgreSQLにアクセスしています。

特定の条件で検索したレコードのIDを取得したいのですが、

その条件でヒットしない場合は新たにレコードを追加してそのIDを取得するということをしたいです。

現在は一度

SELECT id FROM TABLE_A WHERE COLUMN_NAME = foo

した結果をPythonで取り出し、その結果がNoneか値が入っているかで条件分岐しています。

Noneならば

INSERT INTO TABLE_A VALUES(foo)

してから上記のSQL文を実行しています(idはserialで自動発生させています)。

あまりスマートではないのでよりよいSQL文をご存知でしたら

ご指導ご鞭撻のほどよろしくお願いします。

Answer 1

あまりスマートではありませんがSQL一文で行えます。 インサート後のIDが得られます。SELECTと同じ要領で取得できます。

INSERT INTO TABLE_A (COLUMN_NAME)
SELECT 'foo' WHERE NOT EXISTS (
 SELECT 1 FROM TABLE_A WHERE COLUMN_NAME = 'foo'
) RETURNING ID;

INSERT INTO TABLE_A (COLUMN_NAME) VALUES('foo')
  ON CONFLICT ON CONSTRAINT upst_pkey
  DO UPDATE SET COLUMN_NAME='foo'
  RETURNING ID;

Comments

[acre_maker]

ご回答ありがとうございます。
質問がわかりにくくて申し訳ないですが、
このINSERT INTOしたレコードのIDを取り出すところまでをSQL文で書けたらいいな、
というのが質問です。
よろしくお願いします。

[acre_maker]

すいません。少し勘違いをしていました。
RETURNING IDをつけることでINSERTした場合、その値を得ることができるのですね。
ありがとうございます！
こちらで試したところ、当たり前ですがすでにデータがある時は何も値が返ってこないですね。

データがある時、INSERTはせずにそのIDを返す
データがある時、INSERTしてそのIDを返す

という条件分岐のように振る舞うことはできますでしょうか？

[A.Ichi]

postgresのバージョンは9.5以上でしょうか？　upsertが、使えるかどうかです。　
と言いつつ9.5の環境が無いのですぐには確認できませんです。

[acre_maker]

version明記していませんでしたね。失礼しました。version. 9.5.7なのでupsertは使えると思います。
UPSERTと教えていただいたRETURNINGを組み合わせるといけそうですかね？

[A.Ichi]

とは言いましたが、現在当方に9.5の環境が無いのですぐに確認ができません。申し訳ない。

[acre_maker]

アイディアありがとうございます。
UPSERTで
データがすでにある場合は何もせず、
データが無い場合はINSERTする
いずれにせよRETURNING IDと言った感じで行けそうですね！

[A.Ichi]

大変申し訳ございません、DO NOTHINGでのRETURNINGのテストをしたかったのですが。

[A.Ichi]

upsert 試してみました、がDO NOTHINGは行けてないですね、貰えませんでした。同じ値で更新すると得られましたが。

[acre_maker]

私も試してみましたがおっしゃるとおりDO NOTHINGでは帰ってきませんでしたね。おしいですね。
serialの値が進んでしまうのでちょっとダサいですが、ご提示いただいたようにわざと同じ値をUpdateすることで使ってみます。長々とありがとうございました！

[A.Ichi]

同感です。serialはINSERT命令で加算されロールバックもできないのでいつも諦めております。なので2文には成りますがUPDATE、INSERTを行っているケースも有ります。更新も必要な場合どうせselectするなら更新ゼロ件でも同じなかと。

[acre_maker]

おっしゃるようにRETURNINGを用いて2文で書くだけでもスッキリしそうですね。
改めてありがとうございました！