Skip site navigation (1) Skip section navigation (2)

Re: CHECK constraint and trigger

From: "Mikael Carneholm" <carniz(at)spray(dot)se>
To: pgsql-novice(at)postgresql(dot)org
Subject: Re: CHECK constraint and trigger
Date: 2006-11-20 20:07:24
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-novice
> 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

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


Nätets roligaste filmer hittar du på Spray Crazy.

pgsql-novice by date

Next:From: anil maranDate: 2006-11-21 01:59:46
Subject: LOG: logger shutting down
Previous:From: A. KretschmerDate: 2006-11-20 14:15:35
Subject: Re: Using seq. objects with COPY FROM?

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group