Insert row if not already present

Поиск
Список
Период
Сортировка
От Nathaniel Trellice
Тема Insert row if not already present
Дата
Msg-id 461514.98043.qm@web25003.mail.ukl.yahoo.com
обсуждение исходный текст
Ответы Re: Insert row if not already present  (Sean Davis <sdavis2@mail.nih.gov>)
Список 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?

Nathaniel





В списке pgsql-novice по дате отправления:

Предыдущее
От: Thomas Kellerer
Дата:
Сообщение: Re: Unique constraint on only some of the rows
Следующее
От: Sean Davis
Дата:
Сообщение: Re: Insert row if not already present