Skip site navigation (1) Skip section navigation (2)

Re: Copy From & Insert UNLESS

From: Stephan Szabo <sszabo(at)megazone(dot)bigpanda(dot)com>
To: James William Pye <pgsql(at)jwp(dot)name>
Cc: Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: Copy From & Insert UNLESS
Date: 2006-02-05 22:08:12
Message-ID: 20060205135638.G33067@megazone.bigpanda.com (view raw or flat)
Thread:
Lists: pgsql-hackers
On Fri, 3 Feb 2006, James William Pye wrote:

> Despite the fact that my experimental patch uses error trapping, that is *not*
> what I have in mind for the implementation. I do not want to trap errors upon
> insert or copy from. Rather, I wish to implement functionality that would allow
> alternate destinations for tuples that violate user specified constraints on
> the table, which, by default, will be to simply drop the tuple.
>
> My proposed syntax is along the lines of:
>
>    INSERT INTO table [ ( column [, ...] ) ]
> *   [UNLESS CONSTRAINT VIOLATION
>      [ON (constraint_name [, ...]) [THEN INSERT INTO table]] [, OR ...]]
>     { DEFAULT VALUES | VALUES ( { expression | DEFAULT } [, ...] ) | query }
>
>  and
>
>    COPY tablename [ ( column [, ...] ) ]
>     FROM { 'filename' | STDIN }
> *   [UNLESS CONSTRAINT VIOLATION
>      [ON (constraint_name [, ...]) [THEN INSERT INTO table]] [, OR ...]]
>    ...
>
> The purpose of the arguably unintuitive THEN INSERT INTO x portion is to provide
> the mechanism in which a user can specify the destination table for tuples that
> violated the associated set of constraints. Using the OR portion allows the user
> to specify additional sets of constraints for different destinations.
>
> A tuple will be withheld from the target table if ANY of the constraints
> listed in any of the constraint_name sets is violated. Constraint sets should
> not [may not?] reference the same constraint multiple times, even among
> different sets.
>
> Example:
>
>  \d dest_table
>    Table "public.dest_table"
>   Column |  Type   | Modifiers
>  --------+---------+-----------
>   i      | integer | not null
>   j      | integer |
>  Indexes:
>      "dest_table_pkey" PRIMARY KEY, btree (i)
>  Check constraints:
>      "dest_table_j_check" CHECK (j > 0)
>
>  CREATE TEMP TABLE pkey_failures (i int, j int);
>  CREATE TEMP TABLE check_failures (i int, j int);
>
>  COPY dest_table FROM STDIN
>   UNLESS CONSTRAINT VIOLATION
>    ON (dest_table_pkey) THEN INSERT INTO pkey_failures
>    OR (dest_table_j_check) THEN INSERT INTO check_failures;
>
> For most constraints, this proposed implementation should be fairly easy to
> implement.

Have you considered how this might work with spec-compliant constraint
timing?  I think even in inserting cases, a later trigger before statement
end could in some cases un-violate a constraint, so checking before insert
won't actually be the same behavior as the normal constraint handling
which seems bad for this kind of system.

In response to

Responses

pgsql-hackers by date

Next:From: James William PyeDate: 2006-02-06 00:51:01
Subject: Re: Copy From & Insert UNLESS
Previous:From: August ZajoncDate: 2006-02-05 21:49:56
Subject: Re: New project launched : PostgreSQL GUI Installer for Linux/Unix

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group