Re: INSERT only unique records

On Fri, Jul 10, 2009 at 01:32:40PM -0700, Mark Felegyhazi wrote:
> I'm learning Postgresql and trying to inserts only new entries in a
> table and skips duplicates. I know this is a recurrent question, but
> maybe you could point out what I'm doing wrong in this specific case.

> To avoid duplicates, I had the following ideas:
>
> 1. put a unique constraint on num in to_t

Constraints are just there to let you know when you're doing something
that would break the expectations of other bits of your code. They
don't know what to do when these constraints are broken, they just keep
the database in a consistent state so that other code has a chance to do
"the right thing".

> Could you point me to a solution?

The simplest would just be to rewrite your queries as something like:

INSERT INTO to_t (num)
SELECT DISTINCT num
FROM from_t f LEFT JOIN to_t t ON f.num = t.num
WHERE f.num > 2
AND t.num IS NULL;

The SELECT DISTINCT part tells the database to only return distinct
values from the query. The LEFT JOIN tells the database to filter out
anything that already exists in the "to_t" table.

--
Sam http://samason.me.uk/