CUSTOM UNIQUE CHECK POSTGRES

Question

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?

user330315 · Accepted Answer · 2022-08-02 13:43:58Z

2

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.

answered Aug 2, 2022 at 13:43

user330315

Sign up to request clarification or add additional context in comments.

Comments

Jim Jones · Accepted Answer · 2022-08-02 14:20:31Z

1

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

edited Aug 2, 2022 at 14:20

answered Aug 2, 2022 at 13:48

Jim Jones

19.9k3 gold badges44 silver badges51 bronze badges

3 Comments

Ketan Suthar Over a year ago

Thanks for your answer. Although it works I want a solution that does not use an index.

Jim Jones Over a year ago

@KetanSuthar there is no mention of such restriction in your question - which is quite unusual btw :D. Unique indexes are the standard approach to apply such checks: the docs say "Indexes can also be used to enforce uniqueness of a column's value, or the uniqueness of the combined values of more than one column.". Other than that you'll have to rely on extensions like btree_gist or even redesign your data model.

Frank Heikens Over a year ago

If you don't want to use the UNIQUE INDEX syntax, you can use the ALTER TABLE ADD CONSTRAINT syntax to create the constraint. However, adding a unique constraint will automatically create a unique B-tree index on the column or group of columns listed in the constraint.

Collectives™ on Stack Overflow

CUSTOM UNIQUE CHECK POSTGRES

2 Answers 2

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related