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

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