Re: CHECK constraint and trigger

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

Ok. But a DEFERABLE constraint would fix this, right? (if it was possible for CHECK
constraints)

> 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

I just thought that a sum() would be faster than a count(*) (don't ask me why...), and the GROUP
BY is there to make sure only one tuple is returned (more than one row
could be returned by the overlaps())

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

That works perfectly - thanks!

Regards,
Mikael


Nätets roligaste filmer hittar du på Spray Crazy. http://crazy.spray.se/