Re: [HACKERS] Idea on how to simplify comparing two sets
От | Fabien COELHO |
---|---|
Тема | Re: [HACKERS] Idea on how to simplify comparing two sets |
Дата | |
Msg-id | alpine.DEB.2.20.1702071926270.13956@lancre обсуждение исходный текст |
Ответ на | [HACKERS] Idea on how to simplify comparing two sets (Joel Jacobson <joel@trustly.com>) |
Список | pgsql-hackers |
> 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.
В списке pgsql-hackers по дате отправления: