PostgreSQL基于生成字符串属性的Join关联异常问题排查

嘿，这个问题我太熟了！你遇到的是PostgreSQL里to_char函数的一个经典小坑——当你用'Day'作为格式符时，它会自动给返回的星期名称填充尾随空格，让所有结果的长度统一为9个字符。

你看，Wednesday刚好是9个字符，不需要补空格，所以和表中的值完全一致，就能成功关联；而Monday、Tuesday这些短于9个字符的，返回的结果后面都带着空格，和你表中存的无空格版本自然匹配不上啦！

解决办法 #

1. 用FM修饰符去掉尾随空格（最推荐） #

修改子查询里的to_char，加上FM前缀，它会自动去掉格式化后的填充空格，返回干净的星期名称：

select t.date, t.weekday, work_schema_items.weekday
from (
 select dd::date as date, to_char(dd, 'FMDay')::varchar as weekday
 from generate_series('2019-12-08'::timestamp, '2019-12-16'::timestamp, '1 day'::interval) dd
) as t
left join work_schema_items on t.weekday = work_schema_items.weekday
order by date

FMDay会直接返回'Monday'、'Tuesday'这种无空格的字符串，和你表中的数据完美匹配，而且不会影响索引效率。

2. 关联时用trim()去除空格 #

如果不想修改to_char的写法，也可以在关联条件里用trim()函数去掉两边的空格：

select t.date, t.weekday, work_schema_items.weekday
from (
 select dd::date as date, to_char(dd, 'Day')::varchar as weekday
 from generate_series('2019-12-08'::timestamp, '2019-12-16'::timestamp, '1 day'::interval) dd
) as t
left join work_schema_items on trim(t.weekday) = trim(work_schema_items.weekday)
order by date

不过要注意，trim()会让字段上的索引失效（如果你的表有索引的话），所以这种方法只适合小数据量的场景。

3. 改用数字型星期值关联（最稳定） #

如果想彻底避免字符串匹配的问题，还可以用数字形式的星期几来关联。比如用extract(isodow from dd)得到标准的星期数字（1=周一，7=周日），再和表中的星期名称做映射：

select t.date, t.weekday_name, work_schema_items.weekday
from (
 select 
   dd::date as date,
   to_char(dd, 'FMDay') as weekday_name,
   extract(isodow from dd) as weekday_num
 from generate_series('2019-12-08'::timestamp, '2019-12-16'::timestamp, '1 day'::interval) dd
) as t
left join work_schema_items on t.weekday_num = case work_schema_items.weekday
    when 'Monday' then 1
    when 'Tuesday' then 2
    when 'Wednesday' then 3
    when 'Thursday' then 4
    when 'Friday' then 5
    when 'Saturday' then 6
    when 'Sunday' then 7
end
order by date

这种方法完全避开了字符串格式的问题，稳定性最高，适合长期使用。

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