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.
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 |