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

Re: [personal] Re: Filtering duplicated row with a trigger

From: Josh Berkus <josh(at)agliodbs(dot)com>
To: papapep <papapep(at)gmx(dot)net>,pgsql-novice <pgsql-novice(at)postgresql(dot)org>
Subject: Re: [personal] Re: Filtering duplicated row with a trigger
Date: 2003-10-06 17:42:59
Message-ID: 200310061042.59420.josh@agliodbs.com (view raw or flat)
Thread:
Lists: pgsql-novice
Josep,

> I'm very sorry for not being able to explain my problem (BTW, I'm
> spanish speaker...)
> I'll try to do it better.

That was very clear.

> I've got a table that has the following fields:
>
>       F1 F2 F3 F4 F5 .........F16
>
> and we have defined that there can't be any field repeating the fiels
> F1,F2,F5,F14 and F15 (our, complex, primary key).
>
> I've got, on the other hand, text files prepared to be inserted in this
> table with the \copy command, but we are not sure (we've found
> duplicated rows several times) that there are not repeated rows.

I'd suggest using the temporary table (or "holding table") approach suggested 
by other posters on this list.    While you could use a trigger, that would 
mean using INSERT instead of COPY, which would slow down your data loading a 
lot.

What you want to do after loading the table really depends on how you want to 
handle duplicates.  If you just want to ignore them, then use the SELECT 
DISTINCT ON suggestion from another list member ... although this will have 
the defect of grabbing the first row with that primary key and ignoring the 
others, which might have different information in the other columns.

If you want to raise an alarm and halt the import on finding a duplicate., 
then do:

SELECT F1,F2,F5,F14, F15, count(*) as duplicates
FROM holding_table
GROUP BY F1,F2,F5,F14, F15
HAVING count(*) > 1;

The rows returned by that query will show you the primary keys of the 
duplicate rows.

-- 
Josh Berkus
Aglio Database Solutions
San Francisco
-- 
Josh Berkus
Aglio Database Solutions
San Francisco

In response to

pgsql-novice by date

Next:From: Bruno Wolff IIIDate: 2003-10-06 18:01:41
Subject: Re: [personal] Re: Filtering duplicated row with a trigger
Previous:From: Bruno Wolff IIIDate: 2003-10-06 17:40:32
Subject: Re: [personal] Re: Filtering duplicated row with a trigger

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