Prevent INSERT if time overlap in PgSQL without trigger

Question

i would like to prevent an insert into my PgSQL table if the values for a time period (lets call them newStart and newEnd), if this would overlap with any other timeperiod in my rows. So, the table has a bunch of row where each has a start and end date (oldStart and oldEnd).

Is it possible to do this inside the query, without an trigger, an without getting all the data from the db first end check it inside php (because i think this is not ne best/fastest way to do). Please correct me i think wrong. I found the PgSQL overlap function, but building a query was not possible for me! How can i get more in touch with advanced database things? Just by doing?

score 2 · Answer 1 · edited May 23 '17 at 12:12

2

Use an exclusion constraint.

More elegant with actual range types instead of start/end:

Preventing adjacent/overlapping entries with EXCLUDE in PostgreSQL

But you can also use an expression in the constraint. Basically:

CREATE TABLE tbl (
   tbl_id    serial PRIMARY KEY
 , starts_at timestamp
 , ends_at   timestamp
 , EXCLUDE USING gist (tsrange(starts_at, ends_at) WITH &&) -- no overlapping
);

More details:

Postgres constraint for unique datetime range

How can I get more in touch with advanced database things?

Study answers here, read the excellent manual, experiment with newly found techniques.

edited May 23 '17 at 12:12

Community

1
1

answered Feb 28 '15 at 18:45

Erwin Brandstetter

605,456
145
1,078
1,228

"newly found techniques".... Introduced in version 9.0, September 2010 :-) – Frank Heikens Mar 01 '15 at 09:41
Well, "newly found" was general advise for learners, not targeted at exclusion constraints. – Erwin Brandstetter Mar 01 '15 at 09:52

Prevent INSERT if time overlap in PgSQL without trigger

1 Answers1