Re: Conditional INSERT: if not exists - Mailing list pgsql-novice

Don Morrison wrote:

> My problem: if the insert fails because the value already exists, then
> this starts a rollback of my entire transaction.  The solution I'm
> trying is to create a nested transaction with a savepoint right before
> the insert, thus catching the rollback with the nested
> transaction...I'm not sure the nested transaction is necessary...maybe
> just the savepoint. Example:

You need to:

SAVEPOINT insert_may_fail
try:
    INSERT INTO ...
    RELEASE SAVEPOINT
except:
    ROLLBACK TO SAVEPOINT
    UPDATE ...

You can catch the exception either in Python, or if you are using a ZSQL
method, using <dtml-try>.


--
Stuart Bishop <stuart@stuartbishop.net>
http://www.stuartbishop.net/