Re: Insert row if not already present - Mailing list pgsql-novice

Hello all,

I'm looking for some reassurance/advice. Having developed my very-limited
database skills in isolation, using only a postgres manual for reference, I've
probably fallen into some bad habits. They may work, but they're probably far
from being the best way to perform some task.

So, today, I'm considering my usual home-brew solution to the problem of wanting
to add rows to a table, but only if those rows are not already present. Here's a
distilled version of the question:

Suppose you have two tables defined:
 CREATE TABLE table_a (col_a int UNIQUE);
 CREATE TABLE table_b (col_b int);

And they're populated with the following:
 INSERT INTO table_a (col_a) VALUES (1);
 INSERT INTO table_a (col_a) VALUES (2);
 INSERT INTO table_b (col_b) VALUES (2);
 INSERT INTO table_b (col_b) VALUES (3);
 INSERT INTO table_b (col_b) VALUES (3);

So we've got:

 SELECT * FROM table_a;

 col_a
-------
    1
    2
(2 rows)

and

 SELECT * FROM table_b;

 col_b
-------
    2
    3
    3
(3 rows)


I'd like to add all those rows in table_b which are not already present in
table_a, but not include duplicates (to ensure the uniqueness constraint). E.g.
I'd like to end up with:

 SELECT * FROM table_a;

 col_a
-------
    1
    2
    3
(3 rows)

I'm doing this using the following SQL:

 INSERT INTO table_a (col_a)
  SELECT DISTINCT table_b.col_b FROM table_b
  WHERE NOT EXISTS (SELECT 1 FROM table_a WHERE table_a.col_a = table_b.col_b);

Is this a decent way to achieve this? If the real tables contain millions of
rows (say), then assuming both col_a and col_b have bog-standard btree indices,
will this approach scale up efficiently, or is there a better formulation of the
SQL?