Сравнение двух представлений в Postgresql

Вопросы и ответы
, ,
1

Ищем оптимальный по скорости способ, как сверить данные двух представлений в Postrgesql (одно - слепок другого) и на выходе получить набор строк одного представления, отличающихся от строк второго представления.
Подскажите варианты)

2

Сходу напрашивается использование хэширования для набора значений требуемых колонок

-- Создаем тестовые данные
CREATE TABLE t_1  ( 
     id   BIGINT, 
     name TEXT 
  ); 
CREATE TABLE t_2 ( 
     id   BIGINT, 
     name TEXT 
  ); 

INSERT INTO t_1 (id, name) 
VALUES (1, 'apple'), 
       (2, 'samsung');
INSERT INTO t_2 (id, name) 
VALUES (1, 'apple'),
       (2, 'lenovo');

-- Извлекаем несоответствия
SELECT * 
FROM (SELECT t_1.id, 
             md5(CAST(( t_1.* ) AS text)) AS md5_t1, 
             md5(CAST(( t_2.* ) AS text)) AS md5_t2 
      FROM   t_1, t_2 
      WHERE  t_1.id = t_2.id) q1 
WHERE  md5_t1 <> md5_t2;
3

Спасибо! Кажется, то, что нужно!

4

У строк в этих представлениях есть идентификаторы (первичные ключи, primary key, PK)? В зависимости от этого возможны разные способы сравнения.
Если есть идентификаторы, то проще всего, кажется как-то так:

with q_0(id, x, y) as (
  values (1, 'foo', 'bar'), (2, null, 'baz'), (3, 'abc', 'qwe'), (4, null, 'zzz')
),
q_1(id, x, y) as (
  values (1, 'foo2', 'bar'), (2, null, 'baz'), (3, 'abc', null), (4, null, 'zzz')
)
select t_0.*, t_1.*
from q_0 t_0
full join q_1 t_1 on t_0.id = t_1.id
where (t_0.*) is distinct from (t_1.*)

Если идентификаторов нет - то задача немного сложнее, и есть 2 варианта:

  1. сравнивать множества
  2. выравнивать отсортированные множества, чтобы находить замененные строки (т.е., diff-подобный алгоритм).

Для сравнения разницы множеств можно поступить примерно так:

with q_0(x, y) as (
  values ('foo', 'bar'), (null, 'baz'), ('abc', 'qwe'), (null, 'zzz')
),
q_1(x, y) as (
  values ('foo2', 'bar'), (null, 'baz'), ('abc', null), (null, 'zzz')
)
(select *, 'q_0' as side from q_0
except
select *, 'q_0' from q_1)
union all
(select *, 'q_1' from q_1
except
select *, 'q_1' from q_0)

(Замечание: не факт, что эти запросы - самые оптимальные)

2 лайка
5

Inner join не покажет добавленные или удаленные строки.
Хэширование для сравнения можно не использовать, т.к. сравнивать можно строки целиком - сперва получить составное значение (table.*), а затем сравнить их между собой (я точно не помню, как = для составных значений учитывает внутренние null'ы - поэтому проще использовать is distinct from, is not distinct from.