Adding a unique index with a where constraint that includes datetimes-CodePudding

I have a table, voucher_redemption, which has a user_id column and an inserted_at column, whose type is timestamp with timezone.

I'm trying to apply a unique index to only allow one redemption per user per 24 hours, and I am trying this:

CREATE UNIQUE INDEX
  one_redemption_per_user_per_day
ON
  voucher_redemption (user_id)
WHERE
  (inserted_at AT TIME ZONE 'UTC')
BETWEEN
  ((now() - INTERVAL '24 HOURS') AT TIME ZONE 'UTC')
AND
  (now() AT TIME ZONE 'UTC')
;

When I run this, I receive the following:

(Postgrex.Error) ERROR 42P17 (invalid_object_definition) functions in index predicate must be marked IMMUTABLE

According to other posts I've seen online, this is because some timestamps given are not in UTC, but as far as I can see they are.

How can I achieve what I'm trying to?

Thanks in advance

CodePudding user response：

Your result is because the now() function in not immutable. It accesses the server time - which is not 'from the parameters'. You cannot create a unique constraint using it. You can create trigger to effectively accomplish the same. (see demo)

create or replace function validate_voucher_redemption()
   returns trigger 
   language plpgsql
as $$
begin 
   if exists (select null
                from voucher_redemption 
               where user_id = new.user_id
                 and inserted_at at time zone 'UTC'  interval '24 hour' > now() at time zone 'UTC'
             ) 
      then raise exception 'Cannot redeem a second voucher within 24 hours.';  
      else return new; 
   end if;
end; 
$$;

create trigger validate_voucher_redemption_bir
      before insert on voucher_redemption
       for each row 
       execute function validate_voucher_redemption();

NOTE: Everything is in time zone 'UTC' however, this is not required.