Conditional table update. Left join vs NOT IN

From: Allan Kamau <kamauallan(at)gmail(dot)com>
To: Postgres General Postgres General <pgsql-general(at)postgresql(dot)org>
Subject: Conditional table update. Left join vs NOT IN
Date: 2010-08-09 06:20:25
Message-ID: AANLkTimUta+rRvELO_aW6OYnsYNrUUcmGBFph+CsYrkH@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Greetings,
I have a table which I would like to (conditionally and efficiently)
populate if the would be new records do not already exist in that
table.
So far I see that I may use either a left join with a WHERE right
table key field is NULL. Or I could use a sub query and a NOT IN
clause. Perhaps there another better way of doing this.
Which of these options will more likely be more efficient than the other(s)?

1)
INSERT INTO foo
(key_field1,field2)
SELECT
key_fieldA,fieldB
FROM foo2 a
LEFT JOIN
foo b
ON
b.key_fieldA=a.key_field1
WHERE
b.key_fieldA IS NULL
;

2)
INSERT INTO foo
(key_field1,field2)
SELECT
key_fieldA,fieldB
FROM foo2 a
WHERE
a.key_fieldA NOT IN
(
SELECT
a.key_field1
FROM
foo a
)
;

Allan.

Browse pgsql-general by date

  From Date Subject
Next Message Allan Kamau 2010-08-09 06:45:28 Re: MySQL versus Postgres
Previous Message Scott Frankel 2010-08-08 22:51:37 Re: MySQL versus Postgres