Re: Insert row if not already present

On Mon, Jan 24, 2011 at 6:19 AM, Nathaniel Trellice <naptrel(at)yahoo(dot)co(dot)uk>wrote:

> 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?
>
>
Hi, Nathaniel.

If I understand you, then your simplest solution is to use only one table
and include a second column. Then, your first column can have a unique
constraint and the second column can contain "a" or "b".

Sean