Currently there is no simple way to check if two sets are equal.
My 0.02€:
Assuming that you mean set = relation, and that there is a key (which
should be the case for a set otherwise tuples cannot be distinguished, so
this is not really a set), and assuming not null other data, then:
CREATE TABLE TAB1(k INT PRIMARY KEY, data TEXT NOT NULL);
INSERT INTO TAB1 VALUES (1, 'one'), (2, 'two'), (3, 'three');
CREATE TABLE TAB2(k INT PRIMARY KEY, data TEXT NOT NULL);
INSERT INTO TAB2 VALUES (1, 'one'), (2, 'deux'), (4, 'four');
The TAB1 to TAB2 difference is computed with:
SELECT
CASE WHEN t1.k IS NULL THEN 'INSERT'
WHEN t2.k IS NULL THEN 'DELETE'
ELSE 'UPDATE'
END AS operation,
COALESCE(t1.k, t2.k) AS key
FROM TAB1 AS t1
FULL JOIN TAB2 AS t2 USING (k)
WHERE
t1.data IS NULL OR t2.data IS NULL OR t1.data <> t2.data;
Results in:
UPDATE | 2
DELETE | 3
INSERT | 4
If there is no differences, then sets are equals...
If there is no associated data, a simpler condition:
WHERE t1.k IS NULL OR t2.k IS NULL;
--
Fabien.
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
