Date: Wed, 31 Aug 2005 14:09:59 -0700 (PDT)
From: Philip Hallstrom <postgresql@philip.pjkh.com>
To: Ragnar =?ISO-8859-1?Q?Hafsta=F0?= <gnari@simnet.is>
Cc: Henry Ortega <juandelacruz@gmail.com>, pgsql-sql@postgresql.org
Subject: Re: insert only if conditions are met?
In-Reply-To: <1125520078.2605.38.camel@localhost.localdomain>
Message-ID: <20050831140831.Q5678@wolf.pjkh.com>
References: <2bffcc33050831080942f806e1@mail.gmail.com>
	<20050831152534.GA18934@winnie.fuhr.org>
	<2bffcc3305083108487fb80cb2@mail.gmail.com>
	<2bffcc3305083108497ea6dcc6@mail.gmail.com>
	<1125506698.2605.22.camel@localhost.localdomain>
	<2bffcc33050831094957be1b4a@mail.gmail.com>
	<1125520078.2605.38.camel@localhost.localdomain>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII; format=flowed

> On Wed, 2005-08-31 at 12:49 -0400, Henry Ortega wrote:
>> Ok. Here's TABLE A
>>
>> emp            date             hours       type
>> JSMITH       08-15-2005   5             WORK
>> JSMITH       08-15-2005   3             WORK
>> JSMITH       08-25-2005   6             WORK
>>
>> I want to insert the ff:
>> 1.) JSMITH    08-15-2005    8    VAC
>> 2.) DOE        08-16-2005    8    VAC
>>
>> #1 should fail because there is already 8 hours entered as being
>> Worked on 08-15-2005 (same date).
>
> sorry, did not notice the duplicates before my previous reply.
>
> you could do something like
> insert into A select 'JSMITH','08-15-2005',8,'VAC'
>    where
>       8 != (select sum(hours) FROM A
>                    WHERE emp = 'JSMITH'
>                    AND date = '8-15-2005');

Wouldn't that fail if JSMITH had only worked 7 hours on 8-15?  I'm 
guessing he'd still want it to fail since adding that 8 hours ov VAC would 
result in a 15 hour day... so maybe something like?

insert into A select 'JSMITH','08-15-2005',8,'VAC'
WHERE
8 >= 8 + (select sum(hours) FROM A
                     WHERE emp = 'JSMITH'
                     AND date = '8-15-2005');

?