Re: Bulk Insert / Update / Delete

You could create a "virtual" table, that you just inserted to, which had a
"do instead" rule which was a function.

Jon

On Thu, 21 Aug 2003, Philip Boonzaaier wrote:

> Thanks Jason.
>
> The UPDATE part works fine. However, INSERT still gives problems. For now,
> I'm inserting hard coded values, and not from one table into the other.
> I use
>
> UPDATE telephones SET
> telephone_type='CELL',
> telephone_number=836789012
> WHERE data_set='AA'
> AND account_number=8
> AND dependant_number=1
> AND sub_entity='pers'
> AND sub_occur=1
>
> INSERT INTO telephones
> VALUES ('AA',8,1,'pers',1,CELL,836789012);
>
> even if I were getting these values from another table, I'd still have to
> somehow determine if the data EXISTS in the telephones table before deciding
> to UPDATE, or INSERT.
>
> It looks like this is just not possible in a SQL query on its own, but has
> to be done in a higher level language where the condition may be
> successfully processed - i.e. SELECT * WHERE <same WHERE as above>
> and then check IF EXISTS, if so, UPDATE, if not INSERT.
>
> Or would I be able to achieve such functionality by writing a Function ?
>
> Regards
>
> Phil
>
> ----- Original Message -----
> From: Jason Godden <jasongodden(at)optushome(dot)com(dot)au>
> To: Philip Boonzaaier <phil(at)cks(dot)co(dot)za>
> Cc: <pgsql-general(at)postgresql(dot)org>
> Sent: Wednesday, August 20, 2003 10:07 AM
> Subject: Re: [GENERAL] Bulk Insert / Update / Delete
>
>
> Hi Philip,
>
> See:
>
> http://www.postgresql.org/docs/7.3/static/functions-subquery.html
>
> ..for starters.
>
> Essentially, to perform the operation atomically I'd use:
>
> begin;
>
> update <table> set <cols> = <values>, ... where exists (select
> <corresponding
> columns> from <table2> where <table1>.<col> = <table2>.<col> (and).. etc..);
>
> (actually i'd probably use a the from extension here ^^^^ , see example
> below)
>
> insert into <table> <columnlist> select <columns> from <table2> where not
> exists (select <corresponding columns> from <table1> where <table2>.<col> =
> <table1>.<col> (and).. etc..);
>
> commit;
>
> because it's wrapped in a transaction both queries have to work or it's all
> rolled back. This example only applies to comparing two tables. You can
> specify a value list if need be.
>
> As an actual example:
>
> begin;
>
> update table1 set col1 = table2.col1, col2 = table2.col2 from
> table2 where table2.key = table1.key;
>
> (whatever your key may be..)
>
> insert into table1 (col1,col2) select col1,col2 from table2 where not exists
> (select col1,col2 from table1 where table1.col1 = table2.col1 and
> table1.col2
> = table2.col2);
>
> (in this ^^^ I'm assuming your keys are col1 and col2 and so it's not
> consistent with the update but you get the idea.
>
> commit;
>
> Rgds,
>
> Jason
>
> On Wed, 20 Aug 2003 01:03 pm, Philip Boonzaaier wrote:
> > Hi Jason
> >
> > Thanks for your prompt response.
> >
> > I'm pretty new to SQL, so please excuse the following rather stupid
> > question
> >
> >
> > How do I use the (NOT) EXISTS or (NOT) IN clause ? Would it be feasible,
> > using your suggestion, to simply put in two SQL statements, in the same
> > query - first UPDATE when EXISTS, then INSERT when NOT EXISTS, to
> > accomplist this in one go ?
> >
> > Regards
> >
> > Phil
> >
> > ----- Original Message -----
> > From: Jason Godden <jasongodden(at)optushome(dot)com(dot)au>
> > To: Philip Boonzaaier <phil(at)cks(dot)co(dot)za>; <pgsql-general(at)postgresql(dot)org>
> > Sent: Tuesday, August 19, 2003 4:42 PM
> > Subject: Re: [GENERAL] Bulk Insert / Update / Delete
> >
> >
> > Hi Philip,
> >
> > Pg is more ansi compliant than most (GoodThing (TM)). You can use the
> > 'when'
> > conditional but not to do what you need. If I understand you correclty
> you
> > should be able to acheive the same result using two seperate queries and
> > the (NOT) EXISTS or (NOT) IN clause. Failing that have a look at the fine
> > docs on pl/pgsql and other postgresql procedural languages which allow you
> > to use loops and conditional statements like 'if'.
> >
> > Rgds,
> >
> > J
> >
> > On Wed, 20 Aug 2003 12:21 pm, Philip Boonzaaier wrote:
> > > I want to be able to generate SQL statements that will go through a list
> >
> > of
> >
> > > data, effectively row by row, enquire on the database if this exists in
> >
> > the
> >
> > > selected table- If it exists, then the colums must be UPDATED, if not,
> >
> > they
> >
> > > must be INSERTED.
> > >
> > > Logically then, I would like to SELECT * FROM <TABLE>
> > > WHERE ....<Values entered here>, and then IF FOUND
> > > UPDATE <TABLE> SET .... <Values entered here> ELSE
> > > INSERT INTO <TABLE> VALUES <Values entered here>
> > > END IF;
> > >
> > > The IF statement gets rejected by the parser. So it would appear that
> > > PostgreSQL does not support an IF in this type of query, or maybe not at
> > > all.
> > >
> > > Does anyone have any suggestions as to how I can achieve this ?
> > >
> > >
> > > This message is privileged and confidential and intended for the
> > > addressee only. If you are not the intended recipient you may not
> > > disclose, copy or in any way use or publish the content hereof, which is
> > > subject to copyright.If you have received this in error, please destroy
> > > the original message and contact us at postmaster(at)cks(dot)co(dot)za(dot) Any views
> > > expressed in
> >
> > this
> >
> > > message are those of the individual sender, except where the sender
> > > specifically states them to be the view of Computerkit Retail Systems,
> > > its subsidiaries or associates. Please note that the recipient must scan
> > > this e-mail and attachments for viruses. We accept no liability of
> > > whatever nature for any loss, liability,damage or expense resulting
> > > directly or indirectly from this transmission of this message and/or
> > > attachments.
> > >
> > > ---------------------------(end of broadcast)---------------------------
> > > TIP 5: Have you checked our extensive FAQ?
> > >
> > > http://www.postgresql.org/docs/faqs/FAQ.html
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 4: Don't 'kill -9' the postmaster
> >
> > This message is privileged and confidential and intended for the addressee
> > only. If you are not the intended recipient you may not disclose, copy or
> > in any way use or publish the content hereof, which is subject to
> > copyright.If you have received this in error, please destroy the original
> > message and contact us at postmaster(at)cks(dot)co(dot)za(dot) Any views expressed in
> this
> > message are those of the individual sender, except where the sender
> > specifically states them to be the view of Computerkit Retail Systems, its
> > subsidiaries or associates. Please note that the recipient must scan this
> > e-mail and attachments for viruses. We accept no liability of whatever
> > nature for any loss, liability,damage or expense resulting directly or
> > indirectly from this transmission of this message and/or attachments.
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 6: Have you searched our list archives?
> >
> > http://archives.postgresql.org
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: the planner will ignore your desire to choose an index scan if your
> joining column's datatypes do not match
>
> This message is privileged and confidential and intended for the addressee only. If you are not the intended recipient you may not disclose, copy or
> in any way use or publish the content hereof, which is subject to copyright.If you have received this in error, please destroy the original message
> and contact us at postmaster(at)cks(dot)co(dot)za(dot) Any views expressed in this message
> are those of the individual sender, except where the sender specifically
> states them to be the view of Computerkit Retail Systems, its subsidiaries or
> associates. Please note that the recipient must scan this e-mail and attachments for viruses. We accept no liability of whatever nature for any loss,
> liability,damage or expense resulting directly or indirectly from this transmission
> of this message and/or attachments.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend
>