添加一个唯一的索引或约束来检查其他行-Postgres

Question

我有一个包含redemptions的表，每个表都有一个user_id。我试图阻止用户在24小时内进行多次兑换。

到目前为止，我尝试过的SQL是：

ALTER TABLE "redemptions"
ADD CONSTRAINT "one_redemption_per_user_per_twenty_four_hours"
CHECK (
NOT EXISTS(
SELECT u from users WHERE u.inserted_at >= now() + INTERVAL '24 hours'
AND user_id = u.user_id;
)
)

导致** (Postgrex.Error) ERROR 42601 (syntax_error) syntax error at or near ";"

有没有更好的方法来实现我想要做的事情？如果没有，我该如何解决上述问题？

谢谢，标记

编辑：赎回表结构符合要求：

|  id  |  user_id  | voucher_id  |  inserted_at  | updated_at

Answer 1

如手册所述

当前，CHECK表达式不能包含子查询，也不能引用当前行的列以外的变量(见第5.4.1节(。可以引用系统列tableoid，但不能引用任何其他系统柱

即使支持这一点，性能也会很糟糕。

有更好的方法。

确保GiST索引有效：CREATE EXTENSION IF NOT EXISTS btree_gist;

create table redemption(
id bigserial, 
user_id bigint,
voucher_id bigint,
validity tstzrange default (tstzrange(now(),now()+'24 hours'::interval), 
EXCLUDE USING GIST (user_id WITH =, validity WITH &&)
);

该约束确保user_id在给定的时间点上只有一个活动凭证。

示例：

步骤1:用户1刚才使用了代金券2。默认有效期为24小时。

INSERT INTO redemption(user_id,voucher_id) 
VALUES (1,2);

步骤2：用户1尝试在12小时内使用另一张代金券(3(：

INSERT INTO redemption(user_id,voucher_id,validity)
VALUES
(1,3,tstzrange(now()+'12 hours'::interval,now()+'36 hours'::interval));

PostgreSQL回复：

ERROR:  conflicting key value violates exclusion constraint "redemption_user_id_validity_excl"
DETAIL:  Key (user_id, validity)=(1, ["2022-10-28 00:01:56.422834+02","2022-10-29 00:01:56.422834+02")) conflicts with existing key (user_id, validity)=(1, ["2022-10-27 12:00:45.750125+02","2022-10-28 12:00:45.750125+02")).

PostgreSQL会自动检查给定用户的有效性范围是否没有重叠。

EXCLUDE约束会自动创建一个适当的索引，从而保证了性能。以下是psql:报告的表格

d+ redemption
Table "public.redemption"
Column   |   Type    | Collation | Nullable |                 Default                    | Storage  | Stats target | Description 
------------+-----------+-----------+----------+--------------------------------------------+----------+--------------+-------------
id         | bigint    |           | not null | nextval('redemption_id_seq'::regclass)     | plain    |              | 
user_id    | bigint    |           |          |                                            | plain    |              | 
voucher_id | bigint    |           |          |                                            | plain    |              | 
validity   | tstzrange |           |          | tstzrange(now(),now()+'24 hours'::interval | extended |              | 
Indexes:
"redemption_user_id_validity_excl" EXCLUDE USING gist (user_id WITH =, validity WITH &&)
Access method: heap

此外，添加另一个关于凭证唯一性的约束可能是有意义的，以避免凭证被多次使用的情况。

因此：

create table redemption(
id bigserial, 
user_id bigint,
voucher_id bigint UNIQUE,
validity tstzrange default (tstzrange(now(),now()+'24 hours'::interval), 
EXCLUDE USING GIST (user_id WITH =, validity WITH &&)
);