postgresql 如何在表中创建检查,以便用户不能两次前往相同的地点,除非已过去6个月

bihw5rsg  于 2022-11-04  发布在  PostgreSQL
关注(0)|答案(1)|浏览(115)

我需要一种方法来进行检查(如果用户在过去6个月内去过同一个地方,则输入将不允许发生)下面是我的模式:

CREATE SCHEMA code

CREATE TABLE code.place ( 
   pk_place_id VARCHAR(8), 
   place_name VARCHAR (50),
   CONSTRAINT pk_place_id PRIMARY KEY (pk_place_id)
);

CREATE TABLE code.user ( 
   pk_user_id VARCHAR(3), 
   user_name VARCHAR (50),
   CONSTRAINT pk_user_id PRIMARY KEY (pk_user_id)
);

CREATE TABLE code.visit ( 
   pk_user_id VARCHAR(3), 
   pk_place_id VARCHAR(8),
   data DATE,
   CONSTRAINT pk_user_id FOREIGN KEY (pk_user_id) REFERENCES code.user,
   CONSTRAINT pk_place_id FOREIGN KEY (pk_place_id) REFERENCES code.place
);

我有代码显示一个表,其中只有用户没有去过一个地方在过去6个月将出现在表中.但我需要把这作为一个检查在我的访问表,所以没有员工会把这些信息在访问表,但我发现自己不能这样做.

SELECT pk_place_id, pk_user_id, date
    FROM visita_polo
    WHERE EXTRACT(year FROM age(NOW(),data))*12 + EXTRACT(month FROM age(now(),data)) > 6;
6ie5vjzr

6ie5vjzr1#

首先,添加btree_gist扩展名。

CREATE EXTENSION btree_gist;

然后创建具有以下排除项的表。

CREATE TABLE code.visit ( 
   pk_user_id VARCHAR(3)
, pk_place_id VARCHAR(8)
,        data DATE
,  CONSTRAINT pk_user_id FOREIGN KEY (pk_user_id) REFERENCES code.user
,  CONSTRAINT pk_place_id FOREIGN KEY (pk_place_id) REFERENCES code.place
,     EXCLUDE USING gist (pk_user_id WITH =, pk_place_id WITH =, daterange(data, (data + interval '6 months')::date) WITH &&)
);

现在,让我们输入一些数据。

INSERT INTO code.user
     VALUES ('ABC', 'Albert Brian Cohen')
;

INSERT INTO code.place
     VALUES ('MADRID', 'Madrid, Spain')
;

INSERT INTO code.visit
     VALUES ('ABC', 'MADRID', CURRENT_DATE)
;

到目前为止,一切顺利。

INSERT INTO code.visit
     VALUES ('ABC', 'MADRID', CURRENT_DATE + interval '5 months')
;

返回排除约束冲突错误。哎呀!时间不足。

INSERT INTO code.visit
     VALUES ('ABC', 'MADRID', CURRENT_DATE + interval '6 months')
;

插入如预期。一切正常!
数据库会为你做这件事。没有必要在应用层强制执行这件事,特别是因为应用层会受到竞争条件的影响。最好在数据层处理这件事,这样即使是应用程序错误也不会悄悄地破坏你的数据。

相关问题