enforcing data integrity for sequential relations These are my tables:

create table trips (
  trip_id serial primary key,
  trip_nm text
);

create table trip_segments (
  segment_id serial primary key,
  departure_ts timestamp with time zone, -- departure time
  arrival_ts   timestamp with time zone, -- arrival time
  trip_id integer references trips (trip_id)
);


An insert would look like:

insert into trips (trip_nm) values ('my trip');

-- first segment
insert into trip_segments (departure_ts,arrival_ts,trip_id) values
                          ('2013-01-30 12:00', '2013-01-30 20:00', trip_id);

-- second segment
insert into trip_segments (departure_ts,arrival_ts,trip_id) values
                          ('2013-01-30 21:00', '2013-01-30 22:00', trip_id);


Given the above data: I want to ensure that a departure time in the second segment is after the arrival time of the first segment.

Which means the following insert should fail for the second segment:

insert into trip_segments (departure_ts,arrival_ts,trip_id) values
                          ('2013-01-30 19:55', '2013-01-31 22:00', trip_id);


There could be many segments for one trip. There are many trips.

So, how do I do that?

My first thought was to use a check constraint function for the trip_segments table, that looks like:

create function previous_ts(trip_id integer)
  returns timestamp with time zone as $$

  /* do some hackery here using the lag() function */

  return previous_arrival_ts;

$$ language 'sql';


Okay, so I haven't implemented that, but that would be the idea. However, I read (on stackoverflow, lost the answer) that using
a check constraint function is, and I'm para-phrasing, a really bad idea.

Ok, so there must be a better way, maybe using a trigger, I don't know. What's the best way to solve this problem?

I'm using postgresql 9.5
You can make use of an EXCLUSION constraint and tsrange datatype instead of two timestamps.

create table trip_segments (
  segment_id serial primary key,
  travel_ts tstzrange, -- departure time to arrival time
  trip_id integer references trips (trip_id),
  EXCLUDE USING gist (trip_id WITH =, travel_ts WITH &amp;&amp;)
);

-- first segment
insert into trip_segments (travel_ts,trip_id) values
                          ('[2013-01-30 12:00,2013-01-30 20:00)', 1);

-- second segment overlaps and fails
insert into trip_segments (travel_ts,trip_id) values
                          ('[2013-01-30 19:55,2013-01-30 22:00]', 1);


You get:


  ERROR:  conflicting key value violates exclusion constraint "trip_segments_travel_ts_excl"
  DETAIL:  Key (travel_ts)=(["2013-01-30 19:55:00+11","2013-01-30 22:00:00+11"]) conflicts with existing key (travel_ts)=(["2013-01-30 12:00:00+11","2013-01-30 20:00:00+11")).


The exclusion is set-up exclusively for time ranges specific to each trip_id.

insert into trips (trip_nm) values ('their trip'); -- add other trip

-- segment overlaps with trip_id=1, but the exclusion is setup specific to trips
insert into trip_segments (travel_ts,trip_id) values
                          ('[2013-01-30 19:55,2013-01-30 22:00)', 2);


Note that timestamp ranges cannot be specified backwards (ending timestamp must be equal or higher than the starting one), and their boundaries are either inclusive ([]) or exclusive (()) boundaries, being the default [), i.e. an inclusive starting and exclusive ending time.