我有一個包含 的表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
uj5u.com熱心網友回復：
如手冊中所述，在此處輸入鏈接描述
目前，CHECK 運算式不能包含子查詢，也不能參考當前行列以外的變數（參見第 5.4.1 節）。可以參考系統列 tableoid，但不能參考任何其他系統列。
即使它被支持，性能也會很糟糕。
有更好的方法來做到這一點。
如果不存在則創建擴展 btree_gist;
create table redemption(
id bigserial,
user_id bigint,
voucher_id bigint,
validity tstzrange,
EXCLUDE USING GIST (user_id WITH =, validity WITH &&)
);
該約束確保 user_id 在給定時間點只有一個活動憑證。
例子。
步驟1：用戶1剛才使用了憑證2。有效期為 24 小時。
INSERT INTO redemption(user_id,voucher_id,validity)
VALUES
(1,2,tstzrange(now(),now() '24 hours'::interval));
第 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 自動檢查給定用戶的有效范圍內沒有重疊。
CHECK 約束自動創建適當的索引這一事實保證了性能。這里是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 | | | | 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,
EXCLUDE USING GIST (user_id WITH =, validity WITH &&)
);
轉載請註明出處，本文鏈接：https://www.uj5u.com/qiye/521709.html
上一篇：在連接表中僅查找完全匹配