Simple SQL constraint question

Question

I can't get my ahead around what I feel should be a simple SQL constraint. Let's say I have the following table:

create table event(
    eventID    serial PRIMARY KEY,
    eventDate  date,
    start      time,
    end        time
);

And I want a constraint that says any two events cannot overlap; that is to say if two events are on the same day, the start time of one must be after the end time of the other, or vice versa.

In propositional logic, I'd so something like

FORALL e1,e2 in Events, e1.date = e2.date IMPLIES  (e1.start > e2.end OR e2.start > e1.end)

i.e. almost trivial. I'm fairly new to SQL though, and I just can't see how to do the same thing! Any pointers?

Thanks Tom

Out of curiosity, are your records in a fixed _a priori_ time zone? Do events never go past midnight in that time zone? — pilcrow, May 22 '11 at 20:19
why not replace the 3 fields `eventdate`,`start` and `end` with `startdate` and `enddate` this will make checking in a trigger far easier. You're asking for trouble with events that start before midnight, but end after midnight. — Johan, May 22 '11 at 20:47

score 1 · Answer 1 · answered May 22 '11 at 20:14

You would need to use a trigger that checks, on every insert or update, if the new event is on the same date/time as a previous one.

See PostgreSQL documentation for triggers here.

CREATE TRIGGER Trigger_check_overlap
BEFORE INSERT OR UPDATE ON event
FOR EACH ROW
EXECUTE PROCEDURE check_overlap();

Here is the documentation for trigger procedures, take note of the NEW special variable which allows you to single out the element being inserted. So you can compare NEW.date to the dates already in the table.

Why the -1? @Vache, I've got a +1 for your if you show us a reasonable implementation of check_overlap(). — pilcrow, May 22 '11 at 20:25

score 0 · Answer 2 · answered May 22 '11 at 20:18

0

do a little search on sql triggers, they are like events that occurs when u are trying to add, update or delete a record from ur table. there u can write ur script to check ur constraints.

Happy coding.

answered May 22 '11 at 20:18

Boomer

1,468
1
15
19

score 0 · Answer 3 · edited May 23 '17 at 11:55

0

First off, you want to hop to this post, which explains how to optimize this kind of query:

PostgreSQL matching interval between start and end time against timestamp

Once you've added a box column, you want to add an exclude constraint (which will add the index automatically):

exclude (datetime_box with &&) using gist

http://www.postgresql.org/docs/current/static/sql-createtable.html

Also, in case you're not aware, be wary of storing time zones:

http://derickrethans.nl/storing-date-time-in-database.html

edited May 23 '17 at 11:55

Community

1
1

answered May 22 '11 at 20:21

Denis de Bernardy

75,850
13
131
154

The OP needn't add an explicit box column: http://www.depesz.com/index.php/2010/01/03/waiting-for-8-5-exclusion-constraints/ – pilcrow May 22 '11 at 20:55

Simple SQL constraint question

3 Answers3