CODEWITHSUNDEEP
postgresqldatabase-designconstraints
sumit
sumit

Reputation: 436

Prevent overlapping times in start-time, end-time columns

We have a use case where we are storing start_week and end_week in a row and want to make sure that these values across rows are never overlapping i.e. they are exclusive. Examples below

Ideal
code - startw - endw
T1 - 201401 - 201404
T2 - 201405 - 201408
T3 - 201409 - 201416

Not Ideal
code - startw - endw
T1 - 201401 - 201404
T2 - 201403 - 201408
T3 - 201407 - 201408
T3 - 201406 - 201410

Can such a constraint be added in Postgres so errors are caught during insertion? What would be the best approach to tackle such an problem and avoid insertions rather than running checks after data is inserted.

Upvotes: 2

Views: 763

Answers (1)

Craig Ringer
Craig Ringer

Reputation: 324771

PostgreSQL has a feature designed for exactly this - exclusion constraints.

See CREATE TABLE ... EXCLUDE ....

For your purposes you want a constraint on a range. The case you have is covered in the manual.

It's somewhat complicated by your use of a weird format for your dates; you'll need to convert them to a tstzrange for the exclusion constraint.

create table exclude(code text, startw integer, endw integer);

insert into exclude(code, startw, endw) values
('T1', 201401, 201404),
('T2', 201405, 201408),
('T3', 201409, 201416);

You can't just:

alter table exclude 
  add constraint no_overlapping_timestamps 
  EXCLUDE USING gist( tstzrange(startw, endw) WITH && );

because you aren't using real timestamps; you have to convert your week numbers to timestamps in the expression above. I can't do that for you because I don't know how you define "week". If it's the standard way, then:

alter table exclude 
  add constraint no_overlapping_timestamps 
  EXCLUDE USING gist( tstzrange(to_date(startw, 'YYYYWW'), to_date(endw, 'YYYYWW')) WITH && );

Upvotes: 4

Related Questions