From: Markus Schaber <schabi@logix-tt.com>
To: TJ O'Donnell <tjo@acm.org>
Cc: pgsql-sql@postgresql.org
Subject: Re: unique rows
Date: Thu, 21 Sep 2006 09:46:11 +0200
Message-ID: <45124343.1060005@logix-tt.com> (raw)
In-Reply-To: <4512208C.9050607@acm.org>
References: <4512208C.9050607@acm.org>
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
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: schabi@logix-tt.com, tjo@acm.org
Subject: Re: unique rows
In-Reply-To: <45124343.1060005@logix-tt.com>
* 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