对特定文本的唯一约束

create unique index some_name on "order" (user_id, (case when status = 'requested' then 1 else null end));

在这个空值背后有一个想法<&燃气轮机;无效的

也许你最好用 ENUM

示例:

t=# create table "order" (
status text,
user_id int,
time timestamp with time zone
);
CREATE TABLE
Time: 6.345 ms
t=# create unique index some_name on "order" (user_id, (case when status = 'requested' then 1 else null end));
CREATE INDEX
Time: 16.979 ms
t=# insert into "order" select 'requested',1,now();
INSERT 0 1
Time: 17.793 ms
t=# insert into "order" select 'other',1,now();
INSERT 0 1
Time: 1.137 ms
t=# insert into "order" select 'other',1,now();
INSERT 0 1
Time: 6.735 ms
t=# insert into "order" select 'other',1,now();
INSERT 0 1
Time: 0.867 ms
t=# insert into "order" select 'requested',1,now();
ERROR:  duplicate key value violates unique constraint "some_name"
DETAIL:  Key (user_id, (
CASE
    WHEN status = 'requested'::text THEN 1
    ELSE NULL::integer
END))=(1, 1) already exists.
Time: 0.342 ms

试试这个。您应该能够构建表,这样您只能具有请求的状态,但不确定是否允许NULL。然后为user_id和status添加一个唯一的。

create table order(
status text CHECK (status in ('requested'[add more statuses here])) ,
user_id int,
time time stamp with time zone
UNIQUE (status, user_id) 
);