Verificando se duas tabelas têm conteúdo idêntico no PostgreSQL

Você pode usar o EXCEPToperador. Por exemplo, se as tabelas tiverem estrutura idêntica, o seguinte retornará todas as linhas que estão em uma tabela, mas não na outra (portanto, 0 linhas se as tabelas tiverem dados idênticos):

(TABLE a EXCEPT TABLE b)
UNION ALL
(TABLE b EXCEPT TABLE a) ;

Ou com EXISTSpara retornar apenas um valor booleano ou uma string com um dos 2 resultados possíveis:

SELECT CASE WHEN EXISTS (TABLE a EXCEPT TABLE b)
              OR EXISTS (TABLE b EXCEPT TABLE a)
            THEN 'different'
            ELSE 'same'
       END AS result ;

Testado no SQLfiddle

Também não é o que EXCEPTremove duplicatas (isso não deve ser uma preocupação se suas tabelas tiverem alguma PRIMARY KEYrestrição UNIQUE, mas pode ser se você estiver comparando resultados de consultas arbitrárias que podem produzir linhas duplicadas).

Outra coisa que a palavra- EXCEPTchave faz é tratar NULLos valores como idênticos, portanto, se a tabela Ativer uma linha com (1,2,NULL)e a tabela Btiver uma linha com (1,2,NULL), a primeira consulta não mostrará essas linhas e a segunda consulta retornará 'same'se as duas tabelas não tiverem outra linha.

Se você quiser contar essas linhas como diferentes, você pode usar uma variação na FULL JOINresposta do gsiems, para obter todas as linhas (diferentes):

SELECT *
FROM a NATURAL FULL JOIN b
WHERE a.some_not_null_column IS NULL 
   OR b.some_not_null_column IS NULL ;

e para obter uma resposta sim/não:

SELECT CASE WHEN EXISTS
            ( SELECT *
              FROM a NATURAL FULL JOIN b
              WHERE a.some_not_null_column IS NULL 
                 OR b.some_not_null_column IS NULL
            )
            THEN 'different'
            ELSE 'same'
       END AS result ;

Se todas as colunas das duas tabelas não forem anuláveis, as duas abordagens darão respostas idênticas.

Uma opção é usar um FULL OUTER JOIN entre as duas tabelas no seguinte formato:

SELECT count (1)
    FROM table_a a
    FULL OUTER JOIN table_b b 
        USING (<list of columns to compare>)
    WHERE a.id IS NULL
        OR b.id IS NULL ;

Por exemplo:

CREATE TABLE a (id int, val text);
INSERT INTO a VALUES (1, 'foo'), (2, 'bar');

CREATE TABLE b (id int, val text);
INSERT INTO b VALUES (1, 'foo'), (3, 'bar');

SELECT count (1)
    FROM a
    FULL OUTER JOIN b 
        USING (id, val)
    WHERE a.id IS NULL
        OR b.id IS NULL ;

Retornará uma contagem de 2, enquanto:

CREATE TABLE a (id int, val text);
INSERT INTO a VALUES (1, 'foo'), (2, 'bar');

CREATE TABLE b (id int, val text);
INSERT INTO b VALUES (1, 'foo'), (2, 'bar');

SELECT count (1)
    FROM a
    FULL OUTER JOIN b 
        USING (id, val)
    WHERE a.id IS NULL
        OR b.id IS NULL ;

retorna a contagem esperada de 0.

A coisa que eu gosto sobre este método é que ele só precisa ler cada tabela uma vez versus ler cada tabela duas vezes ao usar EXISTS. Além disso, isso deve funcionar para qualquer banco de dados que suporte junções externas completas (não apenas Postgresql).

Eu geralmente desencorajo o uso da cláusula USING, mas aqui está uma situação em que acredito ser a melhor abordagem.

Adendo 2019-05-03:

Se houver um problema com possíveis dados nulos (ou seja, a coluna id não é anulável, mas o val é), você pode tentar o seguinte:

SELECT count (1)
    FROM a
    FULL OUTER JOIN b
        ON ( a.id = b.id
            AND a.val IS NOT DISTINCT FROM b.val )
    WHERE a.id IS NULL
        OR b.id IS NULL ;

Você precisa da cláusula Except Algo como

SELECT * FROM first_table
EXCEPT
SELECT * FROM second_table

Isso retorna todas as linhas da primeira tabela que não estão na segunda tabela

Usar excepté a chave para encontrar o que está na tabela1 e não na tabela2 e vice-versa e unir os resultados. Se forem idênticos, a contagem é zero.

;with CTE1 AS
(
  (
    select column1, column2 from Table1
    except
    select column1, column2 from Table2
  )
  UNION
  (
    select column1, column2 from Table2
    except
    select column1, column2 from Table1
  )
)
Select count(*) From CTE1  -- If identical count should be 0.

Se você não se importa com uma tabela ser menor que a outra, mas se importa apenas com as diferenças nos atributos:

Olhando para o código vinculado que você não entende:

select count(*) from
(
select * From EmpDtl1
union
select * From EmpDtl2
)

O molho secreto está usando unionem oposição a union all. O primeiro retém apenas linhas distintas, enquanto o último mantém duplicatas ( referência ). Em outras palavras, as consultas aninhadas dizem "me dê todas as linhas e colunas de EmpDtl1 e, além disso, aquelas de EmpDtl2 que ainda não estão em EmpDtl1". A contagem desta subconsulta será igual à contagem de EmpDtl1 se e somente se EmpDtl2 não contribuir com nenhuma linha para o resultado, ou seja, as duas tabelas são idênticas.

Como alternativa, despeje as tabelas na sequência de teclas em dois arquivos de texto e use a ferramenta de comparação de sua escolha.

Gambiarra!

Esta é uma solução alternativa para uma verificação rápida e não uma solução profissional. Ele está trabalhando apenas com colunas numéricas como identificadores, caso contrário, converta colunas de string em numéricas (conte as letras, converta em bytes ou similares) se necessário, e NULLs não são problema.

Primeira solução não profissional: amostras

No final, como as consultas demoravam muito em uma tabela de 1 Mio para serem comparadas com outras às vezes com valores NULL nos campos para cuidar também, peguei algumas amostras de números de linha escolhidos para testar a diferença de dois mesas aproximadamente. Eu não encontrei nenhuma diferença, pensando que eles são idênticos de qualquer maneira.

Segunda solução um pouco mais profissional: sum() de colunas

Embora essa amostra parecesse suficiente, eu queria ter certeza e, resumindo todas as colunas numéricas, encontrei diferenças!

SELECT sum(col1), sum(col2) FROM table1
UNION
SELECT sum(col1), sum(col2) FROM table2

A saída é como:

Essa soma de colunas ou de toda a tabela é a verificação rápida, e normalmente já deve responder a pergunta principal e revela mais se você agrupar a tabela por atributos interessantes.

Soma de verificação

É um pouco como a solução CHECKSUM no MySQL .

Mente: MySQL!! , não Postgresql:

CHECKSUM TABLE original_table, backup_table;

Você pode encontrar maneiras melhores em Como posso obter um hash de uma tabela inteira no postgresql? .

A questão aqui é que é apenas um Hash, informações como no meu caso, quando eu pude ver pela soma da col1 que deve haver valores 0 ou NULL, se perde.

Próximo passo: encontrar diferenças

O próximo passo para encontrar as diferenças é então ordenar por col1 em ambas as tabelas, ou agrupar por - count() em col1 se não for único.

Você também já deve saber quais atributos podem ser o problema e verificar que, no meu caso, sei que algum col1 pode ser 0. A soma de 0 não faz sentido, contando aqui, e a soma da outra coluna prova a diferença:

SELECT count(col1), sum(col2) FROM table1 WHERE col1 = 0
UNION
SELECT count(col1), sum(col2) FROM table1 WHERE col1 = 0

Uma contra-consulta:

SELECT count(col1), sum(col2) FROM table1 WHERE col1 <> 0
UNION
SELECT count(col1), sum(col2) FROM table1 WHERE col1 <> 0

e depois de algum tempo, você encontra os candidatos com alguma sorte ou depois de um grupo por visão geral que você pode verificar também subtraindo um do outro, ORDER BY difference DESC.

Talvez execute consultas de longa duração agora

Mas mesmo que você conheça scripts pesados ​​agora para encontrar as diferenças reais em uma verificação completa da mesa, você sabe pelo menos que esperar uma hora ou mais faz sentido. :)

Pequena dica: selecione em uma nova tabela

Pequena dica: se você executar uma consulta tão duradoura, considere a indexação primeiro. Além disso, verifique se você pode criar diretamente uma tabela dos resultados do sql usando

CREATE TABLE MY_TABLE AS SELECT ...

Para que você tenha os resultados não apenas na saída.