pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  

help / color / mirror / Atom feed
From: TJ O'Donnell <tjo@acm.org>
To: PostgreSQL - SQL <pgsql-sql@postgresql.org>
Subject: Re: unique rows
Date: Sat, 23 Sep 2006 07:43:18 -0700
Message-ID: <45154806.2060401@acm.org> (raw)
In-Reply-To: <45124343.1060005@logix-tt.com>
References: <4512208C.9050607@acm.org>
	<45124343.1060005@logix-tt.com>

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
> 



view thread (5+ messages)  latest in thread

Message-ID: <45154806.2060401@acm.org>
Permalink:  ../45154806.2060401@acm.org/
Also on:    postgresql.org/message-id/45154806.2060401@acm.org

 · 

reply
Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: tjo@acm.org
  Subject: Re: unique rows
  In-Reply-To: <45154806.2060401@acm.org>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox