How do I make a policy to stop inserting a row if two values from the table are the same as the valu-CodePudding

I need to check if a booking with the same timestamp and product id has been made before inserting a new row.

I am struggling with making this policy and would be great if anybody could help.

Here is the schema for my table:

create table Bookings (
  id bigint not null primary key,
  created_at timestamp default now(),
  start timestamp default now(),
  name character,
  user_id uuid,
  notes text,
  product_id bigint references Services (id),
  status text not null,
  owner_id uuid default uuid_generate_v4()
);

I have tried using triggers with no luck and I made a function to check if there is a row with the same timestamp and product id with timestamp and product is as input and a bool as output. At first I was thinking I could use this in the policy but I received some errors that the function didn't exist.

create or replace function same_time_bookings(bookingtime timestamp, product int) 
returns boolean
as
$$
declare
  returnbool boolean;
begin
  SELECT case when COUNT(*) > 0 
            then 0
            else 1
       end into returnbool from "Bookings" where 
  product_id = product
  and extract(year from start) = extract(year from bookingtime)
  and extract(month from start) = extract(month from bookingtime)
  and extract(day from start) = extract(day from bookingtime)
  and extract(hour from start) = extract(hour from bookingtime);
  return returnbool;
end;
$$ language plpgsql;

CodePudding user response：

You need to create a unique constraint for 2 columns product_id and start. This way you can have multiple rows with the same product_id but different timestamps.

create table Bookings (
  id bigint not null primary key,
  created_at timestamp default now(),
  start timestamp default now(), 
  product_id bigint,
  UNIQUE (product_id, start) -- Add this
);

Demo in DBfiddle