On Dec 19, 2007 11:04 AM, D'Arcy J.M. Cain <[EMAIL PROTECTED]> wrote:
> On Wed, 19 Dec 2007 23:39:34 +0800 > "[EMAIL PROTECTED]" <[EMAIL PROTECTED]> 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. > Actually, I think the safest way is to do the insert and if it fails (key constraint violation), do the update. 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. 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
_______________________________________________ PyGreSQL mailing list [email protected] http://mailman.vex.net/mailman/listinfo/pygresql
