Re: Most efficient way to insert without duplicates

On Tue, Apr 16, 2013 at 10:51 PM, François Beausoleil
<francois(at)teksol(dot)info>wrote:

> INSERT INTO persona_followers(service_id, follower_id, valid_at)
> SELECT service_id, follower_id, NOW()
> FROM (SELECT DISTINCT service_id, follower_id FROM import) AS import
> WHERE NOT EXISTS(SELECT * FROM persona_followers WHERE import.service_id
> = persona_followers.service_id AND import.follower_id =
> persona_followers.follower_id);
>

Try this for your insert query instead:

insert into persona_followers( service_id, follower_id, valid_at )
select i.service_id, i.follower_id, now()
from import i
left join persona_followers pf on i.service_id = pf.service_id and
i.follower_id = pf.follower_id
where pf.service_id is null

order by 1,2;

This will insert only those rows that are not already present, and involves
no subqueries and only one join.

--
Moshe Jacobson
Nead Werx, Inc. | Manager of Systems Engineering
2323 Cumberland Parkway, Suite 201 | Atlanta, GA 30339
moshe(at)neadwerx(dot)com | www.neadwerx.com

"Quality is not an act, it is a habit." -- Aristotle