🐘

PostgreSQL の UNIQUE INDEX で NULL の重複も許可しないようにする

に公開
PostgreSQL
tech

UNIQUE INDEX での NULL の扱い

UNIQUE INDEXは、デフォルトではNULLを区別できません。
(NULLのレコードを複数入れられてしまう)

たとえば下記のようなテーブルを用意します。

CREATE TABLE sample (
  id serial PRIMARY KEY,
  name text
);

nameカラムにUNIQUE INDEXを設定します。

CREATE UNIQUE INDEX sample_name_idx ON sample (name);

同じ値を入れると、UNIQUE INDEXなので一意制約エラーとなります。

INSERT INTO sample (name) VALUES ('taro');
INSERT INTO sample (name) VALUES ('taro');

postgres=# INSERT INTO sample (name) VALUES ('taro');
INSERT 0 1
postgres=# INSERT INTO sample (name) VALUES ('taro');
ERROR:  duplicate key value violates unique constraint "sample_name_idx"
DETAIL:  Key (name)=(taro) already exists.

NULLはいくつでも入れられます。(NULLの重複は弾けない)

INSERT INTO sample (name) VALUES (NULL);
INSERT INTO sample (name) VALUES (NULL);

postgres=# INSERT INTO sample (name) VALUES (NULL);
INSERT 0 1
postgres=# INSERT INTO sample (name) VALUES (NULL);
INSERT 0 1
postgres=# SELECT * FROM sample;
 id | name
----+------
  1 | taro
  3 |
  4 |
(3 rows)

UNIQUE INDEX で NULL の重複も弾く

PostgreSQL 15 でNULLS NOT DISTINCTというオプションが入りました。

これを使うと、NULLの重複も弾けます。(NULLも他の値と同様に、一つの値として扱われる)

CREATE UNIQUE INDEX sample_name_idx ON sample (name) NULLS NOT DISTINCT;

INSERT INTO sample (name) VALUES (NULL);
INSERT INTO sample (name) VALUES (NULL);

postgres=# INSERT INTO sample (name) VALUES (NULL);
INSERT 0 1
postgres=# INSERT INTO sample (name) VALUES (NULL);
ERROR:  duplicate key value violates unique constraint "sample_name_idx"
DETAIL:  Key (name)=(null) already exists.

どんなときに使えるか

1カラムでNULLの重複も弾きたいみたいなことはあまりないのかもしれない(今までそういうのに遭遇したこと無い)のですが、複合カラムだとそういった時もあるのかもしれません。

たとえば下記のようなテーブルで、

CREATE TABLE sample (
  point1 integer NOT NULL,
  point2 integer,
  point3 integer
);

point1、point2、point3 には

  • 1, NULL, NULL
  • 1, 1, NULL
  • 1, 1, 1
  • 1, 2, NULL
  • 1, 2, 1

のような感じで、point1から順に設定されるので、それらでユニークにしたいといったような場合です。

NULLS NOT DISTINCTが無いPostgreSQL 14以前は、下記のような部分UNIQUE INDEXを複数組み合わせないと弾けませんでした。

CREATE UNIQUE INDEX sample_idx1 ON sample (point1) WHERE point2 IS NULL AND point3 IS NULL;
CREATE UNIQUE INDEX sample_idx2 ON sample (point1, point2) WHERE point3 IS NULL;
CREATE UNIQUE INDEX sample_idx3 ON sample (point1, point2, point3);

これがPostgreSQL 15からは、NULLS NOT DISTINCTで一つのUNIQUE INDEXで弾けるようになります。

CREATE UNIQUE INDEX sample_idx ON sample (point1, point2, point3) NULLS NOT DISTINCT;

Discussion