Skip site navigation (1) Skip section navigation (2)

Re: Conditional INSERT: if not exists

From: "Don Morrison" <donmorrison(at)gmail(dot)com>
To: "Franck Routier" <franck(dot)routier(at)axege(dot)com>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: Conditional INSERT: if not exists
Date: 2006-08-23 19:48:53
Message-ID: aee6519f0608231248ka8af73ax7435400f54787ffd@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-novice
> why not simply put a where condition in you insert :
>
> insert into table values (a,b)
> where not exists (select a,b from table)

The WHERE clause does not fit into the INSERT syntax in that way:

http://www.postgresql.org/docs/8.1/interactive/sql-insert.html

INSERT INTO table [ ( column [, ...] ) ]
    { DEFAULT VALUES | VALUES ( { expression | DEFAULT } [, ...] ) | query }

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:

...other outer transaction work here...

BEGIN;
SAVEPOINT insert_may_fail;
INSERT INTO table ...;
COMMIT;

...continue next outer transaction work here...

In response to

Responses

pgsql-novice by date

Next:From: Richard Broersma JrDate: 2006-08-23 20:04:51
Subject: Re: Passing parameters to postgreSQL from MS Access 2000
Previous:From: Andrej Ricnik-BayDate: 2006-08-23 19:19:59
Subject: Re: protecting a database

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group