pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedinsert only if conditions are met?
14+ messages / 9 participants
[nested] [flat]
* insert only if conditions are met?
@ 2005-08-31 15:09 Henry Ortega <juandelacruz@gmail.com>
0 siblings, 2 replies; 14+ messages in thread
From: Henry Ortega @ 2005-08-31 15:09 UTC (permalink / raw)
To: pgsql-sql
Is there a way to insert a record only if a certain
condition is met?
Something like:
insert into employee values('lastname','firstname',8) where
(condition here.. select sum(ofsomething) from xx where sum(ofsomething)>0 )
Is this possible at all with just plain SQL?
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: insert only if conditions are met?
@ 2005-08-31 15:22 Tom Lane <tgl@sss.pgh.pa.us>
parent: Henry Ortega <juandelacruz@gmail.com>
1 sibling, 0 replies; 14+ messages in thread
From: Tom Lane @ 2005-08-31 15:22 UTC (permalink / raw)
To: Henry Ortega <juandelacruz@gmail.com>; +Cc: pgsql-sql
Henry Ortega <juandelacruz@gmail.com> writes:
> Is there a way to insert a record only if a certain
> condition is met?
> Something like:
> insert into employee values('lastname','firstname',8) where
> (condition here.. select sum(ofsomething) from xx where sum(ofsomething)>0 =
> )
> Is this possible at all with just plain SQL?
Instead of INSERT ... VALUES, use INSERT ... SELECT.
regards, tom lane
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: insert only if conditions are met?
@ 2005-08-31 15:25 Michael Fuhr <mike@fuhr.org>
parent: Henry Ortega <juandelacruz@gmail.com>
1 sibling, 1 reply; 14+ messages in thread
From: Michael Fuhr @ 2005-08-31 15:25 UTC (permalink / raw)
To: Henry Ortega <juandelacruz@gmail.com>; +Cc: pgsql-sql
On Wed, Aug 31, 2005 at 11:09:54AM -0400, Henry Ortega wrote:
> Is there a way to insert a record only if a certain
> condition is met?
>
> Something like:
> insert into employee values('lastname','firstname',8) where
> (condition here.. select sum(ofsomething) from xx where sum(ofsomething)>0 )
See the INSERT documentation; it mentions inserting values from a subquery.
INSERT INTO employee
SELECT 'lastname', 'firstname', 8
WHERE <condition>;
--
Michael Fuhr
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: insert only if conditions are met?
@ 2005-08-31 15:49 Henry Ortega <juandelacruz@gmail.com>
parent: Michael Fuhr <mike@fuhr.org>
0 siblings, 2 replies; 14+ messages in thread
From: Henry Ortega @ 2005-08-31 15:49 UTC (permalink / raw)
To: pgsql-sql
What I am trying to do is
* Insert a record for EMPLOYEE A to TABLE A
IF
the sum of the hours worked by EMPLOYEE A on TABLE A
is not equal to N
Is this possible?
>
>
>
> On 8/31/05, Michael Fuhr <mike@fuhr.org> wrote:
> >
> > On Wed, Aug 31, 2005 at 11:09:54AM -0400, Henry Ortega wrote:
> > > Is there a way to insert a record only if a certain
> > > condition is met?
> > >
> > > Something like:
> > > insert into employee values('lastname','firstname',8) where
> > > (condition here.. select sum(ofsomething) from xx where
> > sum(ofsomething)>0 )
> >
> > See the INSERT documentation; it mentions inserting values from a
> > subquery.
> >
> > INSERT INTO employee
> > SELECT 'lastname', 'firstname', 8
> > WHERE <condition>;
> >
> > --
> > Michael Fuhr
> >
>
>
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: insert only if conditions are met?
@ 2005-08-31 16:40 Chris Travers <chris@metatrontech.com>
parent: Henry Ortega <juandelacruz@gmail.com>
1 sibling, 0 replies; 14+ messages in thread
From: Chris Travers @ 2005-08-31 16:40 UTC (permalink / raw)
To: Henry Ortega <juandelacruz@gmail.com>; pgsql-sql
Henry Ortega wrote:
> What I am trying to do is
> * Insert a record for EMPLOYEE A to TABLE A
> IF
> the sum of the hours worked by EMPLOYEE A on TABLE A
> is not equal to N
>
> Is this possible?
Yes, but we will need to see your database schema to provide examples.
A simple example might be
INSERT INTO table_a
SELECT firstname, lastname FROM table_b
WHERE table_b IN (SELECT id FROM (SELECT id, sum(labor) as total_hours
from table_c group by id) WHERE total_hours <> n);
Best Wishes,
Chris Travers
Metatron Technology Consulting
Attachments:
chris.vcf (text/x-vcard, 125B) download
begin:vcard
fn:Chris Travers
n:Travers;Chris
email;internet:chris@metatrontech.com
x-mozilla-html:FALSE
version:2.1
end:vcard
* Re: insert only if conditions are met?
@ 2005-08-31 16:44 Ragnar Hafstað <gnari@simnet.is>
parent: Henry Ortega <juandelacruz@gmail.com>
1 sibling, 1 reply; 14+ messages in thread
From: Ragnar Hafstað @ 2005-08-31 16:44 UTC (permalink / raw)
To: Henry Ortega <juandelacruz@gmail.com>; +Cc: pgsql-sql
On Wed, 2005-08-31 at 11:49 -0400, Henry Ortega wrote:
> What I am trying to do is
> * Insert a record for EMPLOYEE A to TABLE A
> IF
> the sum of the hours worked by EMPLOYEE A on TABLE A
> is not equal to N
>
> Is this possible?
Sure, given a suitable schema
It is not clear to me, if the hours worked are
to be found in the same table you want to insert
into, or not.
gnari
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: insert only if conditions are met?
@ 2005-08-31 16:49 Henry Ortega <juandelacruz@gmail.com>
parent: Ragnar Hafstað <gnari@simnet.is>
0 siblings, 3 replies; 14+ messages in thread
From: Henry Ortega @ 2005-08-31 16:49 UTC (permalink / raw)
To: Ragnar Hafstað <gnari@simnet.is>; +Cc: pgsql-sql
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).
Any suggestions?
On 8/31/05, Ragnar Hafstað <gnari@simnet.is> wrote:
>
> On Wed, 2005-08-31 at 11:49 -0400, Henry Ortega wrote:
> > What I am trying to do is
> > * Insert a record for EMPLOYEE A to TABLE A
> > IF
> > the sum of the hours worked by EMPLOYEE A on TABLE A
> > is not equal to N
> >
> > Is this possible?
>
> Sure, given a suitable schema
>
> It is not clear to me, if the hours worked are
> to be found in the same table you want to insert
> into, or not.
>
> gnari
>
>
>
>
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: insert only if conditions are met?
@ 2005-08-31 19:54 Jim C. Nasby <jnasby@pervasive.com>
parent: Henry Ortega <juandelacruz@gmail.com>
2 siblings, 1 reply; 14+ messages in thread
From: Jim C. Nasby @ 2005-08-31 19:54 UTC (permalink / raw)
To: Henry Ortega <juandelacruz@gmail.com>; +Cc: Ragnar Hafsta? <gnari@simnet.is>; pgsql-sql
SELECT sum(hours) FROM table WHERE emp_name = 'JSMITH' AND work_date =
'8-15-2005'::date will give you the hours. So...
INSERT INTO table
SELECT blah
WHERE (SELECT sum(hours) FROM table WHERE emp_name = 'JSMITH' AND work_date =
'8-15-2005'::date) != 8
Should do what you want.
On Wed, Aug 31, 2005 at 12:49:14PM -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).
>
> Any suggestions?
>
>
>
> On 8/31/05, Ragnar Hafsta? <gnari@simnet.is> wrote:
> >
> > On Wed, 2005-08-31 at 11:49 -0400, Henry Ortega wrote:
> > > What I am trying to do is
> > > * Insert a record for EMPLOYEE A to TABLE A
> > > IF
> > > the sum of the hours worked by EMPLOYEE A on TABLE A
> > > is not equal to N
> > >
> > > Is this possible?
> >
> > Sure, given a suitable schema
> >
> > It is not clear to me, if the hours worked are
> > to be found in the same table you want to insert
> > into, or not.
> >
> > gnari
> >
> >
> >
> >
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com 512-569-9461
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: insert only if conditions are met?
@ 2005-08-31 20:27 Ragnar Hafstað <gnari@simnet.is>
parent: Henry Ortega <juandelacruz@gmail.com>
2 siblings, 1 reply; 14+ messages in thread
From: Ragnar Hafstað @ 2005-08-31 20:27 UTC (permalink / raw)
To: Henry Ortega <juandelacruz@gmail.com>; +Cc: pgsql-sql
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');
gnari
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: insert only if conditions are met?
@ 2005-08-31 20:50 Scott Marlowe <smarlowe@g2switchworks.com>
parent: Jim C. Nasby <jnasby@pervasive.com>
0 siblings, 0 replies; 14+ messages in thread
From: Scott Marlowe @ 2005-08-31 20:50 UTC (permalink / raw)
To: Jim C. Nasby <jnasby@pervasive.com>; +Cc: Henry Ortega <juandelacruz@gmail.com>; Ragnar Hafsta? <gnari@simnet.is>; pgsql-sql
On Wed, 2005-08-31 at 14:54, Jim C. Nasby wrote:
> SELECT sum(hours) FROM table WHERE emp_name = 'JSMITH' AND work_date =
> '8-15-2005'::date will give you the hours. So...
>
> INSERT INTO table
> SELECT blah
> WHERE (SELECT sum(hours) FROM table WHERE emp_name = 'JSMITH' AND work_date =
> '8-15-2005'::date) != 8
>
> Should do what you want.
I keep thinking a constraint it a better idea here. Or is this too
complex to make it one easily.
Either way, I'd make that last part:
<8 instead of !=8, in case this uses floating point instead of int, and
gets 8.0000000001 or what not in it. Or if someone manages to get it up
to 9 or something.
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: insert only if conditions are met?
@ 2005-08-31 21:09 Philip Hallstrom <postgresql@philip.pjkh.com>
parent: Ragnar Hafstað <gnari@simnet.is>
0 siblings, 1 reply; 14+ messages in thread
From: Philip Hallstrom @ 2005-08-31 21:09 UTC (permalink / raw)
To: Ragnar Hafstað <gnari@simnet.is>; +Cc: Henry Ortega <juandelacruz@gmail.com>; pgsql-sql
> 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');
?
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: insert only if conditions are met?
@ 2005-08-31 21:27 Daryl Richter <daryl@brandywine.com>
parent: Henry Ortega <juandelacruz@gmail.com>
2 siblings, 0 replies; 14+ messages in thread
From: Daryl Richter @ 2005-08-31 21:27 UTC (permalink / raw)
To: Henry Ortega <juandelacruz@gmail.com>; +Cc: Ragnar Hafstað <gnari@simnet.is>; pgsql-sql
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).
>
> Any suggestions?
CREATE TABLE "tablea" (
"emp" varchar(6) NOT NULL,
"date" varchar(10) NOT NULL,
"hours" int NOT NULL,
"type" char(4) NOT NULL
);
grant select, insert, update, delete on tablea to public;
insert into tablea( emp, date, hours, type ) values( 'JSMITH',
'08-15-2005', 5, 'WORK' );
insert into tablea( emp, date, hours, type ) values( 'JSMITH',
'08-15-2005', 3, 'WORK' );
insert into tablea( emp, date, hours, type ) values( 'JSMITH',
'08-25-2005', 6, 'WORK' );
create or replace function overtime( varchar, varchar, int, varchar )
returns void as '
insert into tablea( emp, date, hours, type )
select $1, $2, $3, $4
from tablea where ( select sum( hours ) from tablea where emp =
$1 and date = $2 group by emp, date ) + $3 <= 8
union
select $1, $2, $3, $4
from tablea where( select sum( hours ) from tablea where emp = $1
and date = $2 group by emp, date ) is null
' LANGUAGE SQL;
select overtime( 'JSMITH', '08-15-2005', 8, 'VAC' ); # REJECTED
select overtime( 'JSMITH', '08-16-2005', 8, 'VAC' ); # OK
select overtime( 'JSMITH', '08-25-2005', 2, 'WORK' ); # OK
select * from tablea;
>
>
>
>
> On 8/31/05, Ragnar Hafstað < gnari@simnet.is <mailto:gnari@simnet.is> >
> wrote:
>
> On Wed, 2005-08-31 at 11:49 -0400, Henry Ortega wrote:
>
>>What I am trying to do is
>>* Insert a record for EMPLOYEE A to TABLE A
>>IF
>>the sum of the hours worked by EMPLOYEE A on TABLE A
>>is not equal to N
>>
>>Is this possible?
>
>
> Sure, given a suitable schema
>
> It is not clear to me, if the hours worked are
> to be found in the same table you want to insert
> into, or not.
>
> gnari
>
>
>
>
>
>
>
--
Daryl Richter
Platform Author & Director of Technology
v: 610.361.1000 x202
(( Brandywine Asset Management )
( "Expanding the Science of Global Investing" )
( http://www.brandywine.com ))
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: insert only if conditions are met?
@ 2005-09-02 20:44 Henry Ortega <juandelacruz@gmail.com>
parent: Philip Hallstrom <postgresql@philip.pjkh.com>
0 siblings, 1 reply; 14+ messages in thread
From: Henry Ortega @ 2005-09-02 20:44 UTC (permalink / raw)
To: Philip Hallstrom <postgresql@philip.pjkh.com>; +Cc: pgsql-sql
Thanks for all your answers. Very helpful.
What if after adding all those hours in one long transaction,
I want to send a query to check the MONTHLY TOTAL HOURS
(including those just entered)
and if they exceed N number of hours, all those records added
should *ROLLBACK*?
BEGIN;
insert..........
insert.........
if sum(hours)>N then ROLLBACK
END;
Is that possible? Maybe with just plain SQL? (and one transaction)
On 8/31/05, Philip Hallstrom <postgresql@philip.pjkh.com> wrote:
>
> > 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');
>
> ?
>
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: insert only if conditions are met?
@ 2005-09-02 21:00 Philip Hallstrom <postgresql@philip.pjkh.com>
parent: Henry Ortega <juandelacruz@gmail.com>
0 siblings, 0 replies; 14+ messages in thread
From: Philip Hallstrom @ 2005-09-02 21:00 UTC (permalink / raw)
To: Henry Ortega <juandelacruz@gmail.com>; +Cc: pgsql-sql
On Fri, 2 Sep 2005, Henry Ortega wrote:
> Thanks for all your answers. Very helpful.
> What if after adding all those hours in one long transaction,
> I want to send a query to check the MONTHLY TOTAL HOURS
> (including those just entered)
> and if they exceed N number of hours, all those records added
> should *ROLLBACK*?
>
> BEGIN;
> insert..........
> insert.........
> if sum(hours)>N then ROLLBACK
> END;
>
> Is that possible? Maybe with just plain SQL? (and one transaction)
>
Just add in another where clause using AND and modify the values to sum
the hours for the entire month instead of just the day.
At least I think that would do it.
>
>
> On 8/31/05, Philip Hallstrom <postgresql@philip.pjkh.com> wrote:
>>
>>> 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');
>>
>> ?
>>
>
^ permalink raw reply [nested|flat] 14+ messages in thread
end of thread, other threads:[~2005-09-02 21:00 UTC | newest]
Thread overview: 14+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-08-31 15:09 insert only if conditions are met? Henry Ortega <juandelacruz@gmail.com>
2005-08-31 15:22 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-08-31 15:25 ` Michael Fuhr <mike@fuhr.org>
2005-08-31 15:49 ` Henry Ortega <juandelacruz@gmail.com>
2005-08-31 16:40 ` Chris Travers <chris@metatrontech.com>
2005-08-31 16:44 ` Ragnar Hafstað <gnari@simnet.is>
2005-08-31 16:49 ` Henry Ortega <juandelacruz@gmail.com>
2005-08-31 19:54 ` Jim C. Nasby <jnasby@pervasive.com>
2005-08-31 20:50 ` Scott Marlowe <smarlowe@g2switchworks.com>
2005-08-31 20:27 ` Ragnar Hafstað <gnari@simnet.is>
2005-08-31 21:09 ` Philip Hallstrom <postgresql@philip.pjkh.com>
2005-09-02 20:44 ` Henry Ortega <juandelacruz@gmail.com>
2005-09-02 21:00 ` Philip Hallstrom <postgresql@philip.pjkh.com>
2005-08-31 21:27 ` Daryl Richter <daryl@brandywine.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox