Zenn
🐘

ひとつのSQLでDELETEとINSERTとUPDATEするのがつらかった

2021/06/13に公開
PostgreSQL
SQL
tech

はじめに

もぐりでwebエンジニアしててSQL力が低いので、複雑なクエリ書く必要が出てくると、すぐに時間がとける。
タイトルにあるように「ひとつのSQLでDELETEとINSERTとUPDATE」しようとしたら、なかなかうまくいかなかったので、調べたこととかをメモしておく。

環境

db

postgresql使ってる。

go

開発環境的にgoでsqlxってパッケージを使ってsqlを呼んでDBにアクセスしてる。複数データを使ってクエリしたいときは、追加更新削除したいスライスをpq.StringArrayでわたしてるので、sql的には、arrayとして認識される。

Illustrated Guide to SQLX
Named parameters don't work with IN statement · Issue #485 · jmoiron/sqlx
loops - How to increment all elements in an array or slice in golang - Stack Overflow

コード

最終的にこんな感じ。実際に動かしたコードからは、適当に名前を変えたりしてるから、に動かないかも。
書いてしまえば普通ではあるけども、いろいろ知らなさすぎて時間かかった。
実際はさらにサブクエリをwithでまとめてて、苦しかった。

with deletion as (
delete
from hoges a
using users u
where
  a.user_id = u.id
  and a.name <> all(cast(array['name'] as varchar[]))
  and u.uuid = 'uuid'
)
insert into hoges (
    user_id
  , name
  , created_at
  , updated_at
)
select 
    u.id
  , name
  , now()
  , now()
from unnest(
  cast(array['namea1'] as varchar[])
  ) as tmp(name)
  inner join users u
  on u.user_uuid = 'uuid'
on conflict (
    user_id
  , type
)
do update set
    name = excluded.name
  , updated_at = excluded.updated_at

調べてわかったこと

調べてわかったことを少しメモしておく。

with

PostgreSQL: Documentation: 9.1: WITH Queries (Common Table Expressions)
WITH問い合わせ（共通テーブル式）

平行に処理する部分が理解できてなかった。

This alleviates the effects of the unpredictability of the actual order of row updates, and means that RETURNING data is the only way to communicate changes between different WITH sub-statements and the main query.

このため互いが対象テーブルに行った影響を"見る"ことはできません。これは、行の更新に関する実際の順序が予測できないという影響を軽減し、RETURNINGデータが別のWITH副文と主問い合わせとの間で変更を伝える唯一の手段であることを意味します。

上から順にやってくれるつもりで書いてたら、期待通りうごかなかったりした。

insert

PostgreSQL: Documentation: 9.5: INSERT

ちょっとややこいことしたいときは、データをselect fromでとってきて入れる。

excluded

PostgreSQL: Documentation: 9.5: INSERT

Note that the special excluded table is used to reference values originally proposed for insertion:らしい。
これ知らずにやろうとして、どうやったらいいんやろうとしばらく悩んでた。
sqlは入門の本しか読んでないので、もうちょい小難しい本も読まなあかんのかも。

delete

DELETE
PostgreSQL: Documentation: 10: DELETE
PostgreSQLでJOINした結果をDELETEする | KAILO

他のテーブルの条件を使うときはusing使ったら良さそう。
実際は、usingにつかうテーブルをサブクエリにしてて、サブクエリの条件に一致しない場合、deleteが呼ばれなくて、期待通り動かなかったりした。空のテーブルに見えるのかなと思ってたけど、違うみたい。

array

PostgreSQL: Documentation: 9.2: Arrays
sql - Store select query's output in one array in postgres - Stack Overflow
PostgresQL SQL: Converting results to array - Stack Overflow
PostgreSQL: Documentation: 9.1: Row and Array Comparisons

arrayのイメージがいまいち分かってなかったけど、ここらへんを読みながら試した。
unnestしてテーブルっぽくしたり、allとかanyをつかってwhereの中で使ったりできる。

row_number()

PostgreSQL ROW_NUMBER() Explained with Practical Examples
#PostgreSQL Window関数で行番号や順位を抽出 - Qiita

連番な数字をいれようとおもったら、これ使ったら良いみたい。

WITH ORDINALITY

PostgreSQL: Documentation: 12: 7.2. Table Expressions

arrayの順番を保持しようと思ったら、これを使ったら良いみたい。

おわりに

sql力上がって満足。sqlでわからんことあったら、下記手順で解決するのが良さそう。

  1. stack overflowあたりで似たようなことやってる人をさがす
  2. 公式のドキュメントを読む
  3. 実際に試してみる

日本語情報探すと、やたらスクロールしないとほしい情報にたどり着けない入門記事がひっかかってつらい。はじめから英語情報あたるほうがストレス少ない。

またすぐに、「こんなんやりたいけどわからん」ってなるやろうから、ちょっとずつ試しながら慣れていきたい。

GitHubで編集を提案

Discussion