昔の記事の関連みたいなもの。
【PostgreSQL】レコードが存在すればUPDATE、なければINSERTをSQL文で実現する
最近っぽいやり方で1クエリで実現する。
※PostgreSQLのバージョンが9.5以上でないと使えないはず。
環境：
例として、テーブル定義は下記の状態とする。
|
|
# \d guests Table "public.guests" Column | Type | Modifiers ----------+------------------------+----------- name | character varying(255) | email | character varying(255) | not null password | character varying(255) | Indexes: "guests_pkey" PRIMARY KEY, btree (email) |
emailの有無によりINSERTかUPDATEかを判別するという想定。
テーブルの内容は下記。
|
|
# SELECT * FROM guests; name | email | password ------+-------+---------- aaaa | a@a.a | aaa bbbb | b@b.b | bbb (2 rows) |
emailがPRIMARY KEYの為、当然emailに存在する値をINSERTしようとするとエラーとなる。
|
|
# INSERT INTO guests (name, email, password) VALUES ('cccc', 'a@a.a', 'ccc'); ERROR: duplicate key value violates unique constraint "guests_pkey" DETAIL: Key (email)=(a@a.a) already exists. |
そこで、下記のSQL文とする。
|
|
# INSERT INTO guests (name, email, password) VALUES ('cccc', 'a@a.a', 'ccc') # ON CONFLICT (email) DO UPDATE SET name = 'cccc', password = 'ccc'; INSERT 0 1 |
2行目がカギで、emailが重複していた場合はnameとpasswordをUPDATE、していなければINSERT、となる。
実行後のテーブルの状態は下記。
|
|
# SELECT * FROM guests; name | email | password ------+-------+---------- bbbb | b@b.b | bbb cccc | a@a.a | ccc (2 rows) |
当然、emailが重複していなければ普通にINSERTが行われる。
|
|
# INSERT INTO guests (name, email, password) VALUES ('dddd', 'd@d.d', 'ddd') # ON CONFLICT (email) DO UPDATE SET name = 'dddd', password = 'ddd'; INSERT 0 1 # SELECT * FROM guests; name | email | password ------+-------+---------- bbbb | b@b.b | bbb cccc | a@a.a | ccc dddd | d@d.d | ddd (3 rows) |
注意点として、CONFLICTに指定するカラムはUNIQUEである必要がある。
通常のカラムの場合は動作しない為注意。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
# \d admin Table "public.admin" Column | Type | Modifiers ----------+------------------------+----------- name | character varying(255) | email | character varying(255) | password | character varying(255) | # SELECT * FROM admin; name | email | password ------+-------+---------- aaaa | a@a.a | aaa bbbb | b@b.b | bbb (2 rows) # INSERT INTO admin (name, email, password) VALUES ('cccc', 'a@a.a', 'ccc') ON CONFLICT (email) DO UPDATE SET name = 'cccc', password = 'ccc'; ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification |