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