Postgres导入CSV时如何将空字符串识别为NULL以适配整型字段

错误根本原因 #

你当前使用的NULL as ''参数仅能匹配CSV中未被引号包裹的空值，但你的CSV中空值都是被双引号包裹的""，会被Postgres识别为字符串类型的空值而非NULL，往整型字段插入空字符串自然会触发类型转换报错。 #

解决方案 #

方案1：Postgres 10及以上版本（最简便） #

直接在COPY命令中新增FORCE_NULL参数即可，该参数会强制将被引号包裹的、匹配NULL as ''规则的值识别为NULL：

copy "mytable" from '/path/to/file.csv' with delimiter ',' NULL as '' csv header FORCE_NULL *;

如果只需要处理前4个整型字段，也可以指定列名而非用星号匹配所有列：

copy "mytable" from '/path/to/file.csv' with delimiter ',' NULL as '' csv header FORCE_NULL (col1, col2, col3, col4);

把括号内的col1-col4替换为你表中对应的4个整型字段名即可。

方案2：低版本Postgres兼容方案 #

如果你的Postgres版本低于10不支持FORCE_NULL，可以先导入临时表做数据清洗后再转入正式表：

1. 建立临时表，将待转换的整型字段临时设为text类型避免导入报错：

-- 基于正式表结构创建临时表
CREATE TEMP TABLE tmp_mytable AS SELECT * FROM mytable LIMIT 0;
-- 修改前4个整型字段为text类型
ALTER TABLE tmp_mytable 
ALTER COLUMN 你的第一个整型字段名 TYPE text,
ALTER COLUMN 你的第二个整型字段名 TYPE text,
ALTER COLUMN 你的第三个整型字段名 TYPE text,
ALTER COLUMN 你的第四个整型字段名 TYPE text;

2. 导入CSV到临时表：

copy "tmp_mytable" from '/path/to/file.csv' with delimiter ',' NULL as '' csv header;

3. 清洗空字符串为NULL后插入正式表：

INSERT INTO mytable
SELECT
  NULLIF(你的第一个整型字段名, '')::integer,
  NULLIF(你的第二个整型字段名, '')::integer,
  NULLIF(你的第三个整型字段名, '')::integer,
  NULLIF(你的第四个整型字段名, '')::integer,
  -- 其余字段按你的业务需求处理即可，比如示例中的日期、IP字段可以做格式化处理
  col5, col6, col7,
  TO_TIMESTAMP(col8, 'MM/DD/YYYY HH12:MI:SS AM'),
  TRIM(col9)
FROM tmp_mytable;

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