使用date_part时如何处理空值？附报错及SQL示例

1. 报错原因 #

你写反了date_part的参数顺序。PostgreSQL中date_part的正确语法是：

date_part('提取的字段', 日期值)

你写成了DATE_PART(wt.work_date, 'month')，把日期值和字段位置搞反了，数据库找不到对应参数类型的函数，所以触发报错。

2. 空值与LEFT JOIN的逻辑问题 #

因为你用了LEFT JOIN workers_times，wt.work_date可能为空。如果在WHERE子句里直接过滤DATE_PART(...) = 4这类条件，会自动排除wt.work_date为空的行，相当于把LEFT JOIN变成了INNER JOIN，不符合你保留空值场景的需求。

3. 修正后的SQL方案 #

根据业务需求，有两种处理方式：

方式一：保留所有用户，仅匹配符合日期条件的workers_times记录 #

把日期过滤条件移到LEFT JOIN的ON子句中，这样不会过滤主表users的行：

SELECT u.id as user_id
     , u.firstname
     , u.lastname
     , c.id as company_id
     , c.company
     , wt.start_time as available_at
     , wt.end_time as available_until
FROM users u
LEFT JOIN workers_times wt
       ON wt.user_id = u.id
       AND DATE_PART('month', wt.work_date) = 4 
       AND DATE_PART('day', wt.work_date) = 11
LEFT JOIN clients c
       ON c.id = u.company_id
WHERE u.tenant_id = $2 
  AND u.deleted_by IS NULL 
GROUP BY u.id, u.firstname, u.lastname, c.id, c.company, wt.start_time, wt.end_time
ORDER BY u.id DESC
       , u.created_by DESC 
LIMIT 100

注：PostgreSQL中如果u.id不是主键，GROUP BY需要包含所有SELECT中未使用聚合函数的字段，上面补充了相关字段避免语法错误

方式二：仅保留有符合日期条件的workers_times记录，或work_date为空的用户 #

如果需要在WHERE里过滤，要加上空值判断：

SELECT u.id as user_id
     , u.firstname
     , u.lastname
     , c.id as company_id
     , c.company
     , wt.start_time as available_at
     , wt.end_time as available_until
FROM users u
LEFT JOIN workers_times wt
       ON wt.user_id = u.id
LEFT JOIN clients c
       ON c.id = u.company_id
WHERE u.tenant_id = $2 
  AND u.deleted_by IS NULL 
  AND (
    (wt.work_date IS NOT NULL AND DATE_PART('month', wt.work_date) = 4 AND DATE_PART('day', wt.work_date) = 11)
    OR wt.work_date IS NULL
  )
GROUP BY u.id, u.firstname, u.lastname, c.id, c.company, wt.start_time, wt.end_time
ORDER BY u.id DESC
       , u.created_by DESC 
LIMIT 100

4. 简化日期判断的替代写法 #

可以用EXTRACT函数（和date_part功能类似），或者直接匹配日期值：

-- 直接匹配日期（如果work_date是日期类型）
AND wt.work_date = '2024-04-11'::date
-- 或者用EXTRACT
AND EXTRACT(MONTH FROM wt.work_date) = 4 AND EXTRACT(DAY FROM wt.work_date) = 11

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