Re: CHECK constraint and trigger

"Mikael Carneholm" <carniz(at)spray(dot)se> writes:
> create or replace function not_overlaps(bigint, timestamp, timestamp) returns boolean as
> 'select
> case when sum(id) > 0 then
> false
> else
> true
> end
> from booking
> where resource = $1
> and (start_time, end_time) overlaps ($2, $3)
> and canceled = false
> group by resource'
> language sql;

> alter table booking
> add constraint chk_not_overlaps check (not_overlaps(resource, start_time, end_time));

This really isn't going to work as-is, because the check constraint is
evaluated before the actual row update occurs. This means that the
existing row (with canceled = false) is found by the SQL query --- so
*any* update on a canceled = false row is going to fail, except perhaps
one that updates both start_time and end_time in such a way that they
don't overlap the previous version of the row. You'd need to fix the
query to exclude the specific row being checked --- perhaps pass in the
row's id so you can do that.

Also, why are you checking sum(id) rather than count(*), and what's the
point of the GROUP BY? In fact, all you really care about is existence
of at least one conflicting row, so the right way to code this is along
the lines of

select not exists(select 1 from booking
where id != $1 and
resource = $2 and
(start_time, end_time) overlaps ($3, $4)
and not canceled)

regards, tom lane