PostgreSQL无法识别NULL值问题求助

问题现象 #

在PostgreSQL 9.6的my_table表中，description列存在视觉上显示为NULL的值，但出现以下异常：

• 执行description IS NULL查询无结果，description IS NOT NULL却能返回这些值
• 使用COALESCE(description, 'N/A')无法将其替换为'N/A'，依然显示NULL
• 将数据插入到带有NOT NULL约束和默认值的新表时，这些值依然显示为NULL，默认值未触发

相关查询与DDL #

1. 执行以下查询返回description显示为NULL：

SELECT DISTINCT name, description 
FROM my_table 
WHERE name IN('STUDIO', 'ONE BEDROOM') 
 AND description IS NOT NULL;

2. 执行以下查询无结果：

SELECT DISTINCT name, description 
FROM my_table 
WHERE name IN('STUDIO', 'ONE BEDROOM') 
 AND description IS NULL;

3. 原表DDL片段：

...
    name text COLLATE pg_catalog."default",
    description text COLLATE pg_catalog."default",
...

4. 新表DDL片段：

...
    floorplan_name "character varying(128)" COLLATE pg_catalog."default" NOT NULL DEFAULT 'Unknown'::character varying,
    floorplan_desc "character varying(256)" COLLATE pg_catalog."default" NOT NULL DEFAULT 'Not Provided'::character varying,
...

原因分析 #

这些"伪NULL"值并非真正的PostgreSQL NULL，大概率是以下情况之一：

• 空白字符：值为空格、制表符、换行符等空白字符串，视觉上接近NULL，但属于有效非NULL值
• 不可见特殊字符：比如零宽度空格、控制字符等，客户端显示时无法识别，看起来像NULL
• 客户端显示逻辑：某些工具会将空字符串或空白字符渲染为NULL，但实际数据并非NULL

验证方法 #

通过以下SQL确认值的真实内容：

1. 查看值的长度：

SELECT name, description, LENGTH(description) AS desc_length
FROM my_table 
WHERE name IN('STUDIO', 'ONE BEDROOM');

若desc_length大于0，说明是空白或特殊字符；若为0，则是空字符串。

2. 查看值的十六进制编码：

SELECT name, description, ENCODE(description::bytea, 'hex') AS hex_code
FROM my_table 
WHERE name IN('STUDIO', 'ONE BEDROOM');

通过十六进制码可确定具体字符，比如空格对应20，换行对应0a，零宽度空格对应e2808b。

解决方案 #

1. 处理空白/空字符串 #

查询时替换为指定值：

SELECT 
  name, 
  COALESCE(NULLIF(TRIM(description), ''), 'N/A') AS description
FROM my_table 
WHERE name IN('STUDIO', 'ONE BEDROOM');

直接更新表数据为NULL：

UPDATE my_table
SET description = NULL
WHERE name IN('STUDIO', 'ONE BEDROOM') 
  AND (TRIM(description) = '' OR description = '');

2. 处理特殊不可见字符 #

根据十六进制码替换为NULL，例如零宽度空格：

UPDATE my_table
SET description = NULL
WHERE name IN('STUDIO', 'ONE BEDROOM') 
  AND description = E'\u200B';

3. 插入新表时触发默认值 #

插入时显式处理无效值，让默认值生效：

INSERT INTO new_table (floorplan_name, floorplan_desc)
SELECT 
  CASE WHEN TRIM(name) = '' OR name IS NULL THEN DEFAULT ELSE name END,
  CASE WHEN TRIM(description) = '' OR description IS NULL THEN DEFAULT ELSE description END
FROM my_table 
WHERE name IN('STUDIO', 'ONE BEDROOM');

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