CODEWITHSUNDEEP
sqlpostgresqlselect
JoeBe
JoeBe

Reputation: 1290

postgres fast check if attribute combination also exists in another table

I want to check if the same two attribute values exist in two different tables. If the combination from table_a is not existing in table_b it should be inserted into the select statement table. Right now I have the following query, which is working:

CREATE TABLE table_a (
attr_a integer,
attr_b text,
uuid character varying(200),
CONSTRAINT table_a_pkey PRIMARY KEY (uuid)
);

CREATE TABLE table_b (
attr_a integer,
attr_b text,
uuid character varying(200),
CONSTRAINT table_b_pkey PRIMARY KEY (uuid)
);


SELECT * FROM table_a
        WHERE (table_a.attr_a::text || table_a.attr_b::text) != ALL(SELECT (table_b.attr_a::text || table_b.attr_a::text) FROM table_b)

However, the execution time is pretty long. So I would like to ask if there is a faster solution to check for that.

Upvotes: 0

Views: 816

Answers (1)

Mureinik
Mureinik

Reputation: 311883

Your where clause uses a manipulation of attr_a (casting it to text and concatinating with attr_b), so the index can't be used. Instead of this concatination, why not try a straight-forward exists operator?

SELECT *
FROM   table_a a
WHERE  NOT EXISTS (SELECT *
                   FROM   table_b b
                   WHERE  a.attr_a = b.attr_a AND
                          b.attr_b = b.attr_b)

Upvotes: 1

Related Questions