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