On Fri, May 21, 2010 at 11:46 AM, Gauthier, Dave <dave.gauth...@intel.com> wrote: > Is there a way to temporarily suspend constraint checking for a particular > constraint inside of the transaction, try the insert again, capture the next > violation, then the next, etc... then rollback after all have been collected?
If the constraint has the ability to be defined as deferr-able you can do this. from the online pg documentation: http://www.postgresql.org/docs/8.4/interactive/sql-createtable.html [ CONSTRAINT constraint_name ] { NOT NULL | NULL | UNIQUE index_parameters | PRIMARY KEY index_parameters | CHECK ( expression ) | REFERENCES reftable [ ( refcolumn ) ] [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ] [ ON DELETE action ] [ ON UPDATE action ] } [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ] DEFERRABLE NOT DEFERRABLE This controls whether the constraint can be deferred. A constraint that is not deferrable will be checked immediately after every command. Checking of constraints that are deferrable can be postponed until the end of the transaction (using the SET CONSTRAINTS command). NOT DEFERRABLE is the default. Only foreign key constraints currently accept this clause. All other constraint types are not deferrable. INITIALLY IMMEDIATE INITIALLY DEFERRED If a constraint is deferrable, this clause specifies the default time to check the constraint. If the constraint is INITIALLY IMMEDIATE, it is checked after each statement. This is the default. If the constraint is INITIALLY DEFERRED, it is checked only at the end of the transaction. The constraint check time can be altered with the SET CONSTRAINTS command. IIRC, there is an exception to the deferrable rule. I believe that constraint triggers can also be made to be deferrable. http://www.postgresql.org/docs/8.4/interactive/sql-createconstraint.html -- Regards, Richard Broersma Jr. Visit the Los Angeles PostgreSQL Users Group (LAPUG) http://pugs.postgresql.org/lapug -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
