如何筛选可转换为目标数据类型的合法数据完成SQL导入？

你当前的写法直接用::强转，遇到无法转换为integer的非法值（比如你提到的rep_id为X的行）会直接抛出类型错误终止执行，想要过滤合法数据导入可以参考以下方案：

你提到的测试用data_load表样例如下：

通用兼容方案（支持PostgreSQL 16+、MySQL、SQL Server） #

用TRY_CAST安全转换函数，转换失败会返回NULL而非报错，再通过WHERE条件过滤掉转换结果为NULL的非法行即可：

INSERT INTO rep(rep_id, first_name, last_name, base_salary) 
SELECT DISTINCT 
  TRY_CAST(rep_id AS integer), 
  first_name, 
  last_name, 
  TRY_CAST(base_salary AS integer) 
FROM data_load
WHERE 
  -- 只保留两个数值字段都可正常转换的行
  TRY_CAST(rep_id AS integer) IS NOT NULL
  AND TRY_CAST(base_salary AS integer) IS NOT NULL;

PostgreSQL低版本兼容方案 #

如果使用PostgreSQL 12-15版本，可以用内置的pg_input_is_valid函数判断字符串是否符合目标类型的格式：

INSERT INTO rep(rep_id, first_name, last_name, base_salary) 
SELECT DISTINCT 
  rep_id::integer, 
  first_name, 
  last_name, 
  base_salary::integer 
FROM data_load
WHERE 
  pg_input_is_valid(rep_id, 'integer')
  AND pg_input_is_valid(base_salary, 'integer');

重复值补充处理 #

如果你的目标表rep中rep_id是主键，已经存在的重复rep_id数据会触发主键冲突报错，可以在语句末尾增加冲突处理逻辑跳过重复行：

INSERT INTO rep(rep_id, first_name, last_name, base_salary) 
SELECT DISTINCT 
  TRY_CAST(rep_id AS integer), 
  first_name, 
  last_name, 
  TRY_CAST(base_salary AS integer) 
FROM data_load
WHERE 
  TRY_CAST(rep_id AS integer) IS NOT NULL
  AND TRY_CAST(base_salary AS integer) IS NOT NULL
-- 主键冲突时直接跳过该行，不会抛出错误
ON CONFLICT (rep_id) DO NOTHING;

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