PostgreSQL14环境下INSERT插入新表后数据完整性验证方法咨询

1. 基础行一致性校验 #

首先确认插入的总行数匹配：

• INSERT语句执行完成后，pgAdmin会返回INSERT 0 X的结果，X就是成功插入的行数，先记录这个数值
• 单独执行INSERT语句中的SELECT部分（去掉INSERT头），确认返回的行数和X完全一致
• 直接查询新表总条数二次确认：

SELECT COUNT(*) FROM car_detail;

返回结果必须和X完全相等，说明没有漏插、多插的情况。

2. 字段级准确性校验 #

主键/唯一键校验 #

首先确认主键无重复、无空值：

-- 查重复主键
SELECT id, COUNT(*) FROM car_detail GROUP BY id HAVING COUNT(*) > 1;
-- 查空主键
SELECT COUNT(*) FROM car_detail WHERE id IS NULL;

以上两个查询返回结果必须为空，否则说明插入数据存在主键冲突问题。

全量字段一致性校验 #

用PostgreSQL内置的EXCEPT算子对比插入结果和源SELECT结果的差集：

SELECT COUNT(*) FROM (
  -- 新表的结果集
  SELECT id, make, model, price, year FROM car_detail
  EXCEPT
  -- 你原INSERT语句中的SELECT查询部分，原封不动复制过来
  SELECT dealership_stock.id AS id,
         manufacturers.make AS make,
         models.model_name AS model,
         price_sheet.price AS price,
         model_years.year AS year
  FROM dealership_stock
  INNER JOIN.... -- 这里补全你原来的JOIN逻辑
) AS diff_result;

如果返回结果为0，说明新表所有字段的值和源查询结果完全一致，没有字段值错误的问题。

大表优化技巧：如果数据量超过百万行，EXCEPT执行较慢，可以用校验和对比的方法，分别对新表和源SELECT结果计算全局校验值，对比一致即可证明数据完全相同：

-- 计算新表校验和
SELECT MD5(STRING_AGG(CONCAT(id, make, model, price, year) ORDER BY id)) FROM car_detail;
-- 计算源SELECT结果的校验和，把上面FROM后面的表替换成你的源查询即可
SELECT MD5(STRING_AGG(CONCAT(id, make, model, price, year) ORDER BY id)) FROM (
  -- 这里复制你原INSERT里的SELECT查询
) AS source_result;

两个查询返回的MD5值完全相等就说明数据一致。

3. 业务规则校验 #

根据你的业务要求做自定义校验，常见的校验项包括：

• 非空字段校验：检查表设计中要求非空的字段没有空值，比如：

SELECT COUNT(*) FROM car_detail WHERE make IS NULL OR price IS NULL OR year IS NULL;

• 值域校验：检查字段值在合理范围内，比如价格不能为负、年份不能超出合理区间：

SELECT COUNT(*) FROM car_detail WHERE price < 0 OR year < 1900 OR year > EXTRACT(YEAR FROM NOW())::INT;

• 自定义业务逻辑校验：比如同品牌下的车型是否符合业务规则、价格是否在对应车型的定价区间等，按照你的业务需求写对应查询即可，校验不通过的记录数必须为0。

4. 校验结果留存 #

要证明数据校验无误，可留存以下材料：

• 所有校验SQL的执行截图（pgAdmin4中执行后保留SQL语句和返回结果的完整页面截图）
• 整理校验步骤、执行时间、校验结果的文档，作为数据迁移完成的证明材料。

内容的提问来源于stack exchange，提问作者Minion