Postgres 17

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table otp(purpose int,expires_at timestamptz);

CREATE TABLE

create extension btree_gist;
alter table otp rename column expires_at to valid_between;
alter table otp
  alter column valid_between type tstzrange 
    using tstzrange(valid_between,valid_between+'10 seconds'::interval)
 ,add constraint one_valid_otp_per_purpose_at_a_time 
    exclude using gist ( purpose WITH =
                        ,valid_between WITH &&);

CREATE EXTENSION

ALTER TABLE

ALTER TABLE

insert into otp values
  (1,tstzrange(now(),now()+'5 seconds'::interval)) 
, (2,tstzrange(now(),now()+'5 seconds'::interval));

INSERT 0 2

insert into otp values
  (1,tstzrange(now()+'1 seconds',now()+'6 seconds'::interval));

ERROR:  conflicting key value violates exclusion constraint "one_valid_otp_per_purpose_at_a_time"
DETAIL:  Key (purpose, valid_between)=(1, ["2025-06-18 12:57:38.531808+00","2025-06-18 12:57:43.531808+00")) conflicts with existing key (purpose, valid_between)=(1, ["2025-06-18 12:57:37.530795+00","2025-06-18 12:57:42.530795+00")).

begin;
create view v_otp as select*,not now()<@valid_between as is_expired from otp;
select now();
select*from v_otp;
commit;

BEGIN

CREATE VIEW

now

2025-06-18 12:57:37.532268+00

SELECT 1

purpose
valid_between
is_expired

1
["2025-06-18 12:57:37.530795+00","2025-06-18 12:57:42.530795+00")
f

2
["2025-06-18 12:57:37.530795+00","2025-06-18 12:57:42.530795+00")
f

SELECT 2

COMMIT

begin; select pg_sleep(6); commit;

BEGIN

pg_sleep

SELECT 1

COMMIT

select now();
select*from v_otp;

now

2025-06-18 12:57:43.539171+00

SELECT 1

purpose
valid_between
is_expired

1
["2025-06-18 12:57:37.530795+00","2025-06-18 12:57:42.530795+00")
t

2
["2025-06-18 12:57:37.530795+00","2025-06-18 12:57:42.530795+00")
t

SELECT 2

purpose	valid_between	is_expired
1	["2025-06-18 12:57:37.530795+00","2025-06-18 12:57:42.530795+00")	f
2	["2025-06-18 12:57:37.530795+00","2025-06-18 12:57:42.530795+00")	f