Postgres 9.5.3中to_char子查询NOT IN报错：类型不匹配问题求助

这个问题我之前也碰到过，核心就是类型不兼容闹的：generate_series生成的dates字段是timestamp with time zone类型，而你子查询里用to_char(date_created,'YYYY-MM')返回的是纯文本（text）类型，PostgreSQL没办法直接把时间戳和字符串做相等比较，所以抛出了这个错误。

给你几个靠谱的解决思路：

思路1：统一转成字符串格式比较 #

把generate_series生成的时间戳也转成YYYY-MM格式的字符串，让两边类型完全一致：

SELECT to_char(dates, 'YYYY-MM') AS missing_months
FROM generate_series(
 to_date('2016-01-01', 'YYYY-MM'),
 to_date('2017-01-01', 'YYYY-MM'),
 interval '1 month') AS dates
WHERE to_char(dates, 'YYYY-MM') NOT IN (
 SELECT to_char(date_created,'YYYY-MM') FROM some_table
);

这样两边都是字符串类型，就能正常执行NOT IN比较了。

思路2：用时间类型原生比较（更推荐） #

比起字符串比较，直接用时间类型做匹配性能更好，还能利用date_created字段上的索引。可以把原表的时间字段截断到月份（变成当月第一天的时间戳），和generate_series生成的时间戳直接对比：

SELECT to_char(dates, 'YYYY-MM') AS missing_months
FROM generate_series(
 to_date('2016-01-01', 'YYYY-MM'),
 to_date('2017-01-01', 'YYYY-MM'),
 interval '1 month') AS dates
WHERE dates NOT IN (
 SELECT date_trunc('month', date_created) FROM some_table
);

额外提醒：处理NULL值的坑 #

如果some_table里的date_created存在NULL值，NOT IN查询会直接返回空结果，这时候改用NOT EXISTS会更可靠：

SELECT to_char(dates, 'YYYY-MM') AS missing_months
FROM generate_series(
 to_date('2016-01-01', 'YYYY-MM'),
 to_date('2017-01-01', 'YYYY-MM'),
 interval '1 month') AS dates
WHERE NOT EXISTS (
 SELECT 1 FROM some_table
 WHERE date_trunc('month', date_created) = dates
);

这种写法不受NULL值影响，结果会更准确。

内容的提问来源于stack exchange，提问作者Łukasz D. Tulikowski