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

Wasn't there a feature in some SQL database which was the equivalent of
UPDATE OR INSERT ... based on the primary key? Would this accomplish what
you want (I know that I have a desire for this feature a couple of times,
as I simply have code or triggers to essentially do the equivalent)? Is
this a desirable feature for Postgresql?

Regards,
Ed

On Thu, 21 Aug 2003, Philip Boonzaaier wrote:

> Hi Ron
>
> That is just the point. If Postgres cannot tell me which records exist and
> need updating, and which do not and need inserting, then what can ?
>
> In the old world of indexed ISAM files it is very simple - try to get the
> record ( row ) by primary key. If it is there, update it, if it is not,
> insert it.
>
> Now, one can do this with a higher level language and SQL combined, but is
> SQL that weak ?
>
> What happens when you merge two tables ? Surely SQL must somehow determine
> what needs INSERTING and what needs UPDATING.... Or does one try to merge,
> get a failure, an resort to writing something in Perl or C ?
>
> Please help to un - confuse me !
>
> Regards
>
> Phil
> ----- Original Message -----
> From: Ron Johnson <ron.l.johnson@cox.net>
> To: PgSQL General ML <pgsql-general@postgresql.org>
> Sent: Tuesday, August 19, 2003 6:45 PM
> Subject: Re: [GENERAL] Bulk Insert / Update / Delete
>
>
> On Tue, 2003-08-19 at 22:03, 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
>
> How will you which records were updated, thus able to know which need
> to be inserted?
>
> A temporary table and pl/pgsql should do the trick.
>
> > ----- 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 ?
>
> --
> -----------------------------------------------------------------
> Ron Johnson, Jr. ron.l.johnson@cox.net
> Jefferson, LA USA
>
> 484,246 sq mi are needed for 6 billion people to live, 4 persons
> per lot, in lots that are 60'x150'.
> That is ~ California, Texas and Missouri.
> Alternatively, France, Spain and The United Kingdom.
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>
> This message is privileged and confidential and intended for the addressee only. If you are not the intended
recipientyou 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,
pleasedestroy 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
ofwhatever 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
>