Re: Copy From & Insert UNLESS

On Mon, 6 Feb 2006, Josh Berkus wrote:

> Are you sure that a new type of constraint is the way to go for this?
> It doesn't solve our issues in the data warehousing space.  The spec we
> started with for "Error-tolerant COPY" is:
>
> 1) It must be able to handle parsing errors (i.e. bad char set);
> 2) It must be able to handle constraint violations;
> 3) It must output all row errors to a log or "errors" table which makes
> it possible to determine which input row failed and why;
> 4) It must not slow significantly (like, not more than 15%) the speed of
> bulk loading.
>
> On that basis, Alon started working on a low-level error trapper for
> COPY.   It seems like your idea, which would involve a second constraint
> check, would achieve neigher #1 nor #4.

I think in his system it wouldn't check the constraints twice, it'd just
potentially check them at a different time than the normal constraint
timing, so I think it'd cover #4. I'd wonder if there'd be any possibility
of having violations get unnoticed in that case, but I'm not coming up
with an obvious way that could happen.