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
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