【PostgreSQL】データ比較SQL文 を考える

PostgreSQL

■ はじめに

　データを移行ツールを作成した時に、
移行ツールと元のデータに差異がないことを
PostgreSQLにおいて、簡単に確認できるSQL文を考える

■ 解決案

http://www.ajisaba.net/db/postgresql/table_record_diff.html#UNION
https://qiita.com/damassima/items/1d1ba44be480ec4dc6d4

に記載されていた。

構文

(
  SELECT * FROM [テーブル1]
  EXCEPT ALL SELECT * FROM [テーブル2]
) UNION ALL (
  SELECT * FROM [テーブル2]
  EXCEPT ALL SELECT * FROM [テーブル1]
)

■ サンプル

テーブル定義 

-- 移行元テーブル：customers
DROP TABLE IF EXISTS customers;
CREATE TABLE customers(
  id INTEGER PRIMARY KEY,
  customer_no VARCHAR (50),
  customer_name VARCHAR (50) NOT NULL,
  sex CHAR(1),
  birth_date DATE,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- 移行先テーブル：new_[移行元テーブル名]
DROP TABLE IF EXISTS new_customers;
CREATE TABLE new_customers(
  id INTEGER PRIMARY KEY,
  customer_no VARCHAR (50),
  customer_name VARCHAR (50) NOT NULL,
  sex CHAR(1),
  birth_date DATE,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

元データ 

-- テーブル public.customers: 3 rows のデータをダンプしています
INSERT INTO "customers" ("id", "customer_no", "customer_name", "sex", "birth_date", "created_at") VALUES
    (1, 'X0001', 'Mike', 'm', '1972-08-09', '2020-05-08 11:22:22.222'),
    (2, 'X0002', 'Tom', 'm', '2001-12-29', '2020-05-08 11:22:22.222'),
    (3, 'X0003', 'Naomi', 'f', '1991-10-19', '2020-05-08 11:22:22.222');

先データ（今回は比較用ダミー） 

INSERT INTO "new_customers" ("id", "customer_no", "customer_name", "sex", "birth_date", "created_at") VALUES
    (1, 'X0001', 'Mike', 'm', '1972-08-09', '2020-05-08 11:22:22.222'),
    (2, 'X0002', 'Tom', 'm', '2001-12-29', '2020-05-08 11:22:22.222'),
    (3, 'X0003', 'Naomi', 'f', '1991-10-19', '2020-05-08 11:22:22.222');

-- ダメだった時用のデータ
DELETE FROM new_customers;
INSERT INTO "new_customers" ("id", "customer_no", "customer_name", "sex", "birth_date", "created_at") VALUES
    -- 'Mike' => 'Sam'
    (1, 'X0001', 'Sam', 'm', '1972-08-09', '2020-05-08 11:22:22.222'),
    (2, 'X0002', 'Tom', 'm', '2001-12-29', '2020-05-08 11:22:22.222'),
        -- '1991-10-19' => '1991-11-19'
    (3, 'X0003', 'Naomi', 'f', '1991-11-19', '2020-05-08 11:22:22.222');

SQL

(
  SELECT * FROM customers
  EXCEPT ALL SELECT * FROM new_customers
) UNION ALL (
  SELECT * FROM new_customers
  EXCEPT ALL SELECT * FROM customers
)

Case１：同じデータ場合の出力結果 

データなし

Case２：異なるデータ場合の出力結果 

id,customer_no,customer_name,sex,birth_date,created_at
3,X0003,Naomi,f,1991-10-19,2020-05-08 11:22:22.222
1,X0001,Mike,m,1972-08-09,2020-05-08 11:22:22.222
3,X0003,Naomi,f,1991-11-19,2020-05-08 11:22:22.222
1,X0001,Sam,m,1972-08-09,2020-05-08 11:22:22.222

参考文献

http://www.ajisaba.net/db/postgresql/table_record_diff.html#UNION
https://qiita.com/damassima/items/1d1ba44be480ec4dc6d4

関連記事

CSVデータをPostgreSQLにインポートする ～ execute_values() 編 ～
https://dk521123.hatenablog.com/entry/2020/05/09/113559