postgres -避免创建重复的空列

Question

我在Postgres上有这个表模式:

> d+ users_types_brands
Table "public.users_types_brands"
Column     |            Type             | Collation | Nullable |                    Default                     | Storage | Stats target | Description 
----------------+-----------------------------+-----------+----------+------------------------------------------------+---------+--------------+-------------
id             | integer                     |           | not null | nextval('users_types_brands_id_seq'::regclass) | plain   |              | 
inserted_at    | timestamp without time zone |           |          | now()                                          | plain   |              | 
updated_at     | timestamp without time zone |           |          | now()                                          | plain   |              | 
users_types_id | bigint                      |           |          |                                                | plain   |              | 
brand_id       | bigint                      |           | not null |                                                | plain   |              | 
tasks_type_id  | integer                     |           |          |                                                | plain   |              | 
Indexes:
"users_types_brands_pkey" PRIMARY KEY, btree (id)
"users_types_brands_users_types_id_brand_id_tasks_type_id_index" UNIQUE, btree (users_types_id, brand_id, tasks_type_id)
Foreign-key constraints:
"users_types_brands_users_types_id_fkey" FOREIGN KEY (users_types_id) REFERENCES users_types(id)
Access method: heap

现在这个表是这样的:

my_db=# select * from users_types_brands;
id |        inserted_at         |         updated_at         | users_types_id | brand_id | tasks_type_id 
----+----------------------------+----------------------------+----------------+----------+---------------
12 | 2021-10-24 16:43:12.244026 | 2021-10-24 16:43:12.244026 |              2 |      112 |             8
14 | 2021-10-24 17:03:12.012874 | 2021-10-24 17:03:12.012874 |              2 |      111 |             9
(2 rows)

当然，我不能像这样插入一行:

my_db=# insert into users_types_brands (users_types_id, brand_id, tasks_type_id) values (2, 112, 8);
ERROR:  duplicate key value violates unique constraint "users_types_brands_users_types_id_brand_id_tasks_type_id_index"
DETAIL:  Key (users_types_id, brand_id, tasks_type_id)=(2, 112, 8) already exists.

但是我可以这样做几次:

my_db=# insert into users_types_brands (users_types_id, brand_id) values (2, 112);
INSERT 0 1

得到:

my_db=# select * from users_types_brands;
id |        inserted_at         |         updated_at         | users_types_id | brand_id | tasks_type_id 
----+----------------------------+----------------------------+----------------+----------+---------------
12 | 2021-10-24 16:43:12.244026 | 2021-10-24 16:43:12.244026 |              2 |      112 |             8
14 | 2021-10-24 17:03:12.012874 | 2021-10-24 17:03:12.012874 |              2 |      111 |             9
16 | 2021-10-24 17:15:58.295428 | 2021-10-24 17:15:58.295428 |              2 |      112 |              
17 | 2021-10-24 17:16:36.99971  | 2021-10-24 17:16:36.99971  |              2 |      112 |              
(4 rows)

现在，根据业务规则，tasks_type_id可以为null👍

但是我怎么能避免创建重复行像最后两个?一个空的tasks_type_id是可以的，但不能有两个或两个以上。

以前有人遇到过这种情况吗?

Answer 1

可以创建部分唯一索引。它将允许单行具有相同的users_type_id和brand_id和空tasks_type_id，但只有一个。(见演示)

create unique index tasks_type_id_just_1_unique
on users_types_brands (users_types_id, brand_id)
where tasks_type_id is null;

Answer 2

这个问题有两种基本的解决方案，但都有各自的缺点。

1。使用部分索引，正如Belayer指出的那样。缺点是对于非空值，您将需要另一个部分索引，因为这个部分索引将忽略非空值，只覆盖带有空值的行。

CREATE UNIQUE INDEX "index_for_nulls" ON "table" ( "field_a", "field_b" ) WHERE "field_c" IS NULL;
CREATE UNIQUE INDEX "index_for_non_nulls" ON "table" ( "field_a", "field_b", "field_c" ) WHERE "field_c" IS NOT NULL;

2。在索引定义中使用COALESCE来避免空值。这样，index将覆盖所有行，但如果不使用索引

中定义的精确语句，planner将不会使用完整的索引

CREATE UNIQUE INDEX "index" ON "table" ( "field_a", "field_b", ( COALESCE( "field_c", -1 ) );