Re: Bulk Insert / Update / Delete - Mailing list pgsql-general
From | Jonathan Bartlett |
---|---|
Subject | Re: Bulk Insert / Update / Delete |
Date | |
Msg-id | Pine.GSU.4.44.0308261119400.5080-100000@eskimo.com Whole thread Raw |
In response to | Re: Bulk Insert / Update / Delete ("Philip Boonzaaier" <phil@cks.co.za>) |
List | pgsql-general |
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@optushome.com.au> > To: Philip Boonzaaier <phil@cks.co.za> > Cc: <pgsql-general@postgresql.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@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 > > > ---------------------------(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 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 >
pgsql-general by date: