List Info

Thread: How to insert a record if it doesn't exist else update it ?
How to insert a record if it doesn't exist else update it ?
country flaguser name
China		 2007-12-19 09:39:34 
Hi all,

What I want to do is:
     if a record already exists in the table, update it
     if a record doesn't exist, insert it into the table

I know that there is a ON DUPLICATE clause with MySQL, so
I'm wondering is 
there a corresponding way to do this in PostgreSQL ?

Currently the only way I can find is do query first and then
update or insert.

Thanks a lot.

Regards,
_______________________________________________
PyGreSQL mailing list
PyGreSQLVex.Net
http
://mailman.vex.net/mailman/listinfo/pygresql
Re: How to insert a record if it doesn't exist else update it ?
country flaguser name
Canada		 2007-12-19 10:04:07 
On Wed, 19 Dec 2007 23:39:34 +0800
"fdu.xiaojfgmail.com" <fdu.xiaojfgmail.com> wrote:
> Hi all,
> 
> What I want to do is:
>      if a record already exists in the table, update
it
>      if a record doesn't exist, insert it into the
table

First of all, this is a PostgreSQL question, not a PyGreSQL
one.  In
general you will get much better responses if you use the
correct
mailing list.  Check out http://www.PostgreSQL.org/
 for their mailing
lists.  You probably want the SQL or help list.

> I know that there is a ON DUPLICATE clause with MySQL,
so I'm wondering is 
> there a corresponding way to do this in PostgreSQL ?

I don't think so but you can probably do something with
rules.  Check
out the PostgreSQL docs.

> Currently the only way I can find is do query first and
then update or insert.

I think that you will find this the safest way.  If you know
that you
aren't in the middle of a transaction you could use a
try/except
statement but this will break your transaction if you are in
one.

-- 
D'Arcy J.M. Cain
PyGreSQL Development Group
http://www.PyGreSQL.org
_______________________________________________
PyGreSQL mailing list
PyGreSQLVex.Net
http
://mailman.vex.net/mailman/listinfo/pygresql
Re: How to insert a record if it doesn't exist else update it ?
user name
 2007-12-19 10:35:06 

  


  

    

On Dec 19, 2007 11:04 AM, D'Arcy J.M. Cain < darcypygresql.org">darcypygresql.org> wrote:

On Wed, 19 Dec 2007 23:39:34 +0800
&quot; fdu.xiaojfgmail.com">fdu.xiaojfgmail.com" < fdu.xiaojfgmail.com">fdu.xiaojfgmail.com> wrote:
>; Hi all,
>
> What I want to do is:

>      if a record already exists in the table, update it
>      if a record doesn't exist, insert it into the table

First of all, this is a PostgreSQL question, not a PyGreSQL one.  In
general you will get much better responses if you use the correct

mailing list.  Check out http://www.PostgreSQL.org/ for their mailing
lists.  You probably want the SQL or help list.

>; I know that there is a ON DUPLICATE clause with MySQL, so I'm wondering is

> there a corresponding way to do this in PostgreSQL ?

I don't think so but you can probably do something with rules.  Check
out the PostgreSQL docs.

>; Currently the only way I can find is do query first and then update or insert.


I think that you will find this the safest way.  If you know that you
aren&#39;t in the middle of a transaction you could use a try/except
statement but this will break your transaction if you are in one.



Actually, I think the safest way is to do the insert and if it fails (key constraint violation), do the update.&nbsp; You can use a savepoint if you like inside the transaction and roll back to the savepoint if keeping larger transaction semantics are important.  If you do a select query first, then someone might do an insert before you get yours done and your code will break.&nbsp; Of course, this will depend a bit on the semantics of your database, but you get the idea. ; 


As for details of HOW to do this, do a search of the postgresql archives--there are numerous posts on the subject.

Sean





  

  
  
   
     
    

  

    Re:  How to insert a record if it
doesn't exist else update it ?
  


  

     country flaguser name
Germany		
     2007-12-19 15:17:00
  


  

    
fdu.xiaojfgmail.com wrote:
> Hi all,
> 
> What I want to do is:
>      if a record already exists in the table, update
it
>      if a record doesn't exist, insert it into the
table
> 
> I know that there is a ON DUPLICATE clause with MySQL,
so I'm wondering is 
> there a corresponding way to do this in PostgreSQL ?

You can find something equivalent in Example 37-1 in the
Postgres Docs:
http://www.postgresql.org/docs/8.2
/interactive/plpgsql-control-structures.html

Unfortunately, Postgres so far does not support the
"MERGE" statement as 
newer versions of Oracle or SQL server do.

-- Chris

(And as D'Arcy already mentioned, the PostgreSQL mailing
list is a 
better place for such questions.)
_______________________________________________
PyGreSQL mailing list
PyGreSQLVex.Net
http
://mailman.vex.net/mailman/listinfo/pygresql



  


  

    
  

  
  
   
     
    

  

    Re:  How to insert a record if it
doesn't exist else update it ?
  


  

     user name

     2007-12-20 13:45:05
  


  

    
fdu.xiaojfgmail.com wrote:

> What I want to do is:
>      if a record already exists in the table, update
it
>      if a record doesn't exist, insert it into the
table

UPDATE foo SET ...
   WHERE EXISTS (SELECT 1 FROM foo ...);

INSERT INTO foo
   SELECT ... FROM ... WHERE NOT EXISTS (SELECT 1 FROM foo
...);

The 2nd statement is executed if the first is false.  Not
race-proof,
and can't be, even with transactions, so be sure to execute
it in a
sequential manner.


-- 
Regards,
  Daryl Tester

"Verbogeny is one of the pleasurettes of a creatific
thinkerizer."
  --  Peter da Silva
_______________________________________________
PyGreSQL mailing list
PyGreSQLVex.Net
http
://mailman.vex.net/mailman/listinfo/pygresql



  


  

    
  

  
  
   
     
    






 [1-5]











   
 





about | contact  Other archives ( Real Estate discussion Medical topics )