Josh Berkus <josh@agliodbs.com> writes:
>> I'm planning to something like
>> INSERT INTO first_table
>> SELECT * FROM second_table s WHERE
>> NOT EXISTS (SELECT 1 FROM first_table WHERE id = s.id)
> This is the way to do it, athough I'd fully qualify both "id" fields in the
> subquery and use an alias to avoid confusion, e.g.
> NOT EXISTS (SELECT 1 FROM first_table f2 WHERE f2.id = s.id)
Since the tables have identical column sets, you could also think about
INSERT INTO first_table
(SELECT * FROM second_table EXCEPT SELECT * FROM first_table)
which might or might not run considerably faster. It'd be worth trying
I think.
regards, tom lane