Re: getting all constraint violations

On Fri, May 21, 2010 at 11:46 AM, Gauthier, Dave
<dave(dot)gauthier(at)intel(dot)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