Postgresql

檢查兩個表在PostgreSQL中是否具有相同的內容

  • March 3, 2022

這已經在 Stack Overflow 上被問過,但僅適用於 MySQL。我正在使用 PostgreSQL。不幸(並且令人驚訝)PostgreSQL 似乎沒有類似CHECKSUM table.

PostgreSQL 解決方案會很好,但通用的解決方案會更好。我找到了http://www.besttechtools.com/articles/article/sql-query-to-check-two-tables-have-identical-data,但我不明白使用的邏輯。

背景:我重寫了一些數據庫生成程式碼,所以我需要檢查新舊程式碼是否產生相同的結果。

一種選擇是以以下形式在兩個表之間使用 FULL OUTER JOIN:

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 ;

例如:

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 ;

將返回計數 2,而:

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 ;

返回希望計數為 0。

我喜歡這種方法的一點是,它只需要讀取每個表一次,而不是使用 EXISTS 時讀取每個表兩次。此外,這應該適用於任何支持完全外連接的數據庫(不僅僅是 Postgresql)。

我通常不鼓勵使用 USING 子句,但在這種情況下,我認為它是更好的方法。

附錄 2019-05-03:

如果可能的空數據存在問題(即 id 列不可為空,但 val 是),那麼您可以嘗試以下操作:

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 ;

引用自:https://dba.stackexchange.com/questions/72641