Re: Bulk Insert / Update / Delete - Mailing list pgsql-general
From | Philip Boonzaaier |
---|---|
Subject | Re: Bulk Insert / Update / Delete |
Date | |
Msg-id | 011c01c36812$cb6b1b00$e701f00a@240.1.139.196.23.149.50 Whole thread Raw |
In response to | Bulk Insert / Update / Delete ("Philip Boonzaaier" <phil@cks.co.za>) |
Responses |
Re: Bulk Insert / Update / Delete
(Ron Johnson <ron.l.johnson@cox.net>)
Re: Bulk Insert / Update / Delete (Edmund Dengler <edmundd@eSentire.com>) |
List | pgsql-general |
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 recipient youmay 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 destroythe 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.
pgsql-general by date: