CUSTOM UNIQUE CHECK POSTGRES-CodePudding

I am working on a task where I need to store the interviewer's time slot in the table INTERVIEW_SLOT. The table schema is like this:

CREATE TABLE INTERVIEW_SLOT (
    ID SERIAL PRIMARY KEY NOT NULL,
    INTERVIEWER INTEGER REFERENCES USERS(ID) NOT NULL,
    START_TIME TIMESTAMP NOT NULL, -- start time of interview
    END_TIME TIMESTAMP NOT NULL, -- end time of interview
    IS_BOOKED BOOL NOT NULL DEFAULT 'F', -- slot is booked by any candidate or not
    CREATED_ON TIMESTAMP,
    -- interviewer can't give the same slot twice
    CONSTRAINT UNIQUE_INTERVIEW_SLOT UNIQUE (start_time, INTERVIEWER)
);

We want to ensure that the interviewer can not give the same slot twice but the problem is with second and millisecond values of start_time. I want the UNIQUE_INTERVIEW_SLOT constant like this:

UNIQUE_INTERVIEW_SLOT UNIQUE(TO_TIMESTAMP(start_time::text, 'YYYY-MM-DD HH24:MI'), INTERVIEWER)

Is there any way to add a unique constraint that ignores the second and millisecond value?

CodePudding user response：

You are looking for an exclusion constraint

create table interview_slot 
(
  id integer primary key generated always as identity,
  interviewer integer references users(id) not null,
  start_time timestamp not null, -- start time of interview
  end_time timestamp not null, -- end time of interview
  is_booked bool not null default 'f', -- slot is booked by any candidate or not
  created_on timestamp,
  constraint unique_interview_slot 
    exclude using gist (interviewer with =, 
                        tsrange(date_trunc('minute', start_time), date_trunc('minute', end_time), '[]') with &&)
);

This prevents rows with overlapping start/end ranges for the same interviewer. The timestamps are "rounded" to the full minute. You need the extension btree_gist in order to create that constraint.

CodePudding user response：

You can use an UNIQUE INDEX to make this check for you and truncate the timestamp to minutes:

CREATE UNIQUE INDEX idx_interview_slot_ts 
ON interview_slot (interviewer, date_trunc('minutes',start_time));

Demo: db<>fiddle