PostgreSQLでUPSERTをするにはCREATE UNIQUE INDEXでUNIQUEインデックスを作成するのではなくALTER TABLEでUNIQUE制約を作成する必要がある

長いがタイトルの通り。

結論

CREATE UNIQUE INDEX は UNIQUEインデックス を作る。ALTER TABLE ... ADD CONSTRAINT ... UNIQUE は UNIQUE制約 を作る。

値を重複させないための目的であればどちらを使っても問題ないが、UPSERT句（INSERT INTO ... ON CONFLICT ON CONSTRAINT ...）を使いたい場合は UNIQUE制約 で作らないと指定できない。

検証

テーブル例

こういうテーブルを用意したうえでの説明。

CREATE TABLE config (
  key character varying(32) NOT NULL,
  value character varying(100) NOT NULL,
  updated_at bigint NOT NULL
);

CREATE UNIQUE INDEXでUNIQUEインデックスを作る

作成。

CREATE UNIQUE INDEX config_unique on config(key);

\d config で定義の確認。

Indexes:
    "config_unique" UNIQUE, btree (key)

UNIQUEと出る。

削除。

DROP INDEX config_unique;

ALTER TABLEでUNIQUE制約を作る

作成。

ALTER TABLE config_unique ADD CONSTRAINT config_unique UNIQUE (key);

\d config で定義の確認。

Indexes:
    "config_unique" UNIQUE CONSTRAINT, btree (key)

こちらはUNIQUE CONSTRAINTSと出る。

削除

ALTER TABLE config_unique DROP CONSTRAINT config_unique;

UPSERT句で使う

たとえばこんなUPSERTをしたいとする。

INSERT INTO config (key,value,updated_at) VALUES ('moge', 'fuga', 1234567890) ON CONFLICT ON CONSTRAINT config_unique DO UPDATE SET value='fuga', updated_at= 1234567890;

UNIQUE制約だとふつうに成功するが、UNIQUEインデックスだと下記のようなエラーが出る。

ERROR:  constraint "config_unique" for table "config" does not exist

いやUNIQUEインデックスいるやろがい！！と小一時間悩んでいたが、調べると上記に記した UNIQUE制約 と UNIQUEインデックス の違いだった。

まとめ

普段は UNIQUE制約 と UNIQUEインデックス どっち使ってもいいけれど、UPSERT使うときは UNIQUE制約 を使え。