PostgreSQL导入CSV遇NULL数值错误，咨询批量处理方案

不需要手动修改所有列，PostgreSQL提供两种高效的批量处理方案：

方案一：直接修改COPY命令（推荐） #

PostgreSQL的COPY命令支持NULL参数，可指定CSV中代表空值的字符串。只需在WITH子句中添加NULL 'NULL'，就能让PostgreSQL自动把CSV里的"NULL"字符串解析为真正的空值（NULL），无需修改列定义或CSV文件。

修改后的COPY命令：

COPY ireland_income_gap_bonus
FROM 'E:\Programming\SQL\Project\Data\Ireland_gender_pays_gap\Ireland_gpg.CSV'
WITH (FORMAT CSV, HEADER, NULL 'NULL')

这个方法一步到位，直接解决所有列的问题，完全不用手动处理每个numeric列。

方案二：临时表过渡（适用于特殊场景） #

如果因限制无法使用NULL参数，可以先创建结构与原表一致但所有列均为text类型的临时表，导入数据后再转换插入到正式表，同时将"NULL"字符串替换为NULL：

1. 创建临时表：

CREATE TEMP TABLE temp_ireland_income_gap_bonus AS
SELECT * FROM ireland_income_gap_bonus LIMIT 0;
ALTER TABLE temp_ireland_income_gap_bonus
ALTER COLUMN id_c TYPE text,
ALTER COLUMN companyName TYPE text,
ALTER COLUMN companies_ID TYPE text,
ALTER COLUMN meanBonus TYPE text,
ALTER COLUMN meanHourly TYPE text,
ALTER COLUMN medianBonus TYPE text,
ALTER COLUMN medianHourly TYPE text,
ALTER COLUMN reportLink TYPE text,
ALTER COLUMN year_ TYPE text,
ALTER COLUMN meanHourlyPT TYPE text,
ALTER COLUMN medianHourlyPT TYPE text,
ALTER COLUMN meanHourlyTemp TYPE text,
ALTER COLUMN medianHourlyTemp TYPE text,
ALTER COLUMN perBonusFemale TYPE text,
ALTER COLUMN perBonusMale TYPE text,
ALTER COLUMN perBIKFemale TYPE text,
ALTER COLUMN perBIKMale TYPE text,
ALTER COLUMN pb1Female TYPE text,
ALTER COLUMN pb1Male TYPE text,
ALTER COLUMN pb2Female TYPE text,
ALTER COLUMN pb2Male TYPE text,
ALTER COLUMN pb3Female TYPE text,
ALTER COLUMN pb3Male TYPE text,
ALTER COLUMN pb4Female TYPE text,
ALTER COLUMN pb4Male TYPE text,
ALTER COLUMN perEmployeesFemale TYPE text,
ALTER COLUMN perEmployeesMale TYPE text,
ALTER COLUMN commentss TYPE text;

2. 导入数据到临时表：

COPY temp_ireland_income_gap_bonus
FROM 'E:\Programming\SQL\Project\Data\Ireland_gender_pays_gap\Ireland_gpg.CSV'
WITH (FORMAT CSV, HEADER);

3. 转换数据插入正式表：

INSERT INTO ireland_income_gap_bonus
SELECT
    NULLIF(id_c, 'NULL')::smallint,
    NULLIF(companyName, 'NULL'),
    NULLIF(companies_ID, 'NULL')::smallint,
    NULLIF(meanBonus, 'NULL')::numeric(6,2),
    NULLIF(meanHourly, 'NULL')::numeric(6,2),
    NULLIF(medianBonus, 'NULL')::numeric(6,2),
    NULLIF(medianHourly, 'NULL')::numeric(6,2),
    NULLIF(reportLink, 'NULL'),
    NULLIF(year_, 'NULL')::smallint,
    NULLIF(meanHourlyPT, 'NULL')::numeric(6,2),
    NULLIF(medianHourlyPT, 'NULL')::numeric(6,2),
    NULLIF(meanHourlyTemp, 'NULL')::numeric(6,2),
    NULLIF(medianHourlyTemp, 'NULL')::numeric(6,2),
    NULLIF(perBonusFemale, 'NULL')::numeric(6,2),
    NULLIF(perBonusMale, 'NULL')::numeric(6,2),
    NULLIF(perBIKFemale, 'NULL')::numeric(6,2),
    NULLIF(perBIKMale, 'NULL')::numeric(6,2),
    NULLIF(pb1Female, 'NULL')::numeric(6,2),
    NULLIF(pb1Male, 'NULL')::numeric(6,2),
    NULLIF(pb2Female, 'NULL')::numeric(6,2),
    NULLIF(pb2Male, 'NULL')::numeric(6,2),
    NULLIF(pb3Female, 'NULL')::numeric(6,2),
    NULLIF(pb3Male, 'NULL')::numeric(6,2),
    NULLIF(pb4Female, 'NULL')::numeric(6,2),
    NULLIF(pb4Male, 'NULL')::numeric(6,2),
    NULLIF(perEmployeesFemale, 'NULL')::numeric(6,2),
    NULLIF(perEmployeesMale, 'NULL')::numeric(6,2),
    NULLIF(commentss, 'NULL')
FROM temp_ireland_income_gap_bonus;

该方法需要编写全列转换逻辑，不如方案一简洁，仅推荐在无法使用NULL参数的特殊场景下使用。

原问题信息 #

建表语句：

CREATE TABLE ireland_income_gap_bonus (
    id_c smallint,
    companyName text, companies_ID smallint, 
    meanBonus numeric(6,2),
    meanHourly numeric(6,2),
    medianBonus numeric(6,2),
    medianHourly numeric(6,2),
    reportLink text,
    year_ smallint,
    meanHourlyPT numeric(6,2),
    medianHourlyPT numeric(6,2),
    meanHourlyTemp numeric(6,2),
    medianHourlyTemp numeric(6,2),
    perBonusFemale numeric(6,2),
    perBonusMale numeric(6,2),
    perBIKFemale numeric(6,2),
    perBIKMale numeric(6,2),
    pb1Female numeric(6,2),
    pb1Male numeric(6,2),
    pb2Female numeric(6,2),
    pb2Male numeric(6,2),
    pb3Female numeric(6,2), 
    pb3Male numeric(6,2),
    pb4Female numeric(6,2), 
    pb4Male numeric(6,2),
    perEmployeesFemale numeric(6,2),
    perEmployeesMale numeric(6,2),
    commentss text
)

原COPY命令：

COPY ireland_income_gap_bonus
FROM 'E:\Programming\SQL\Project\Data\Ireland_gender_pays_gap\Ireland_gpg.CSV'
WITH (FORMAT CSV, HEADER)

错误信息：

ERROR:  invalid input syntax for type numeric: "NULL" CONTEXT:  COPY
ireland_income_gap_bonus, line 2, column meanhourlypt: "NULL"
SQL state: 22P02

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