Re: Bulk Insert / Update / Delete - Mailing list pgsql-general

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@optushome.com.au>
> To: Philip Boonzaaier <phil@cks.co.za>; <pgsql-general@postgresql.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@cks.co.za. 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@cks.co.za. 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