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

Re: CHECK constraint and trigger

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "Mikael Carneholm" <carniz(at)spray(dot)se>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: CHECK constraint and trigger
Date: 2006-11-20 03:17:27
Message-ID: 21704.1163992647@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-novice
"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

In response to

pgsql-novice by date

Next:From: Yadnyesh JoshiDate: 2006-11-20 05:05:24
Subject: Aggregate Functions
Previous:From: Richard Broersma JrDate: 2006-11-19 16:39:14
Subject: Re: SSL

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