ブラックボックス

プログラミングのノウハウやライフハックをどんどん投稿していきたい

Jul 25, 2017 - 2 minute read - ノウハウ

【PostgreSQL】レコードが存在すればUPDATE、なければINSERTを1クエリで実現する

昔の記事の関連みたいなもの。
【PostgreSQL】レコードが存在すればUPDATE、なければINSERTをSQL文で実現する
最近っぽいやり方で1クエリで実現する。

※PostgreSQLのバージョンが9.5以上でないと使えないはず。

環境:

  • PostgreSQL:9.6.3

例として、テーブル定義は下記の状態とする。

# \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である必要がある。
通常のカラムの場合は動作しない為注意。

# \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