Message-ID: <45154806.2060401@acm.org>
Date: Sat, 23 Sep 2006 07:43:18 -0700
From: TJ O'Donnell <tjo@acm.org>
User-Agent: Thunderbird 1.5.0.7 (Windows/20060909)
MIME-Version: 1.0
To: PostgreSQL - SQL <pgsql-sql@postgresql.org>
Subject: Re: unique rows
References: <4512208C.9050607@acm.org> <45124343.1060005@logix-tt.com>
In-Reply-To: <45124343.1060005@logix-tt.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Markus, Kaloyan, and all

Thanks for the help on this.  I got the trigger to work properly,
although I discovered that a trigger in SQL is not allowed, so I
wrote it in plsql.  It was very, very slow.  So I finally decided
to filter the input before attempting to Copy it into the table,
using a perl hash to ensure uniqueness.  As a side benefit, I was able 
to count the frequency of each input string while I was filtering and 
include that in the final table.

TJ O'Donnell

Markus Schaber wrote:
> Hi, TJ,
> 
> TJ O'Donnell wrote:
> 
>> So, is there a way (a constraint, a check?) that will simply
>> REJECT a duplicate when I insert the raw data directly into x
>> rather than FAILING on an error as it does
>> with the unique constraint above?
> 
> Failing on an error is exactly the way PostgreSQL (and the SQL standard)
> uses to REJECT duplicates. :-)
> 
> You seem to think about silently dropping the duplicates. That could be
> achieved with an BEFORE INSERT trigger, or with a rule on a view, as
> both can silently drop the inserted rule.
> 
> The trigger body could even be in language SQL, along the lines of:
> 
> SELECT CASE WHEN EXISTS (SELECT keycol FROM table WHERE
> table.keycol=NEW.keycol) THEN NULL ELSE NEW;
> 
> Nevertheless, expect the insert performance to drop a little, due to the
> trigger overhead.
> 
> The alternative approaches (SELECT'ing from the application, using a
> stored procedure that checks and then inserts the data, and using
> subtransactions to roll back the failing inserts) all seem worse (uglier
> and slower) to me, but don't hesitate to ask if you're interested.
> 
> HTH,
> Markus
>