Re: Conditional INSERT: if not exists

> 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...