postgres选择部分或全部记录-在查询中使用null

Question

我想要postgres中的一些内容，就像我们在sql中编写过滤查询一样，我们需要返回一些匹配或所有记录。

WITH myvars (t_state, t_crashtype, t_speedzone)as (
values('ABC', 'Intersection', 50))
SELECT 
dr_sex, 
COUNT(*) as all_crashes, 
COUNT(t1.id) filter (WHERE severity_id >= 3) as fsi_crashes,
COUNT(t1.id) filter (WHERE severity_id = 3) as si_crashes,
COUNT(t1.id) filter (WHERE severity_id = 4) as fatal_crashes
FROM 
t1
JOIN 
t2 ON t1.id = t2.crash_id
LEFT JOIN 
( 
SELECT id, lga_name19 FROM geom
) t3 ON t2.lga_id = t3.id
,myvars
WHERE 
(jurisdiction = t_state OR t_state = null)
AND (crash_type_std = t_crashtype OR t_crashtype = null)
AND (speed_zone = t_speedzone OR t_speedzone = null)

AND dr_sex = ANY( '{Male, Female}'::text[] ) 
GROUP BY dr_sex

speed_zone列是可为null的整数，只要我将t_speedzone设置为null

运算符不存在：integer=text

此外，如果我将t_state设置为null，则结果集中不会返回任何内容

Answer 1

我使用了强制转换，还将=null替换为is-null

WITH myvars (t_state, t_crashtype, t_speedzone)as (
values(null, 'Intersection', null))
SELECT 
dr_sex, 
COUNT(*) as all_crashes, 
COUNT(t1.id) filter (WHERE severity_id >= 3) as fsi_crashes,
COUNT(t1.id) filter (WHERE severity_id = 3) as si_crashes,
COUNT(t1.id) filter (WHERE severity_id = 4) as fatal_crashes
FROM 
t1
JOIN 
t2 ON t1.id = t2.crash_id
LEFT JOIN 
( 
SELECT id, lga_name19 FROM geom
) t3 ON t2.lga_id = t3.id
,myvars
WHERE 

(jurisdiction = t_state OR t_state is null)
AND (crash_type_std = t_crashtype OR t_crashtype is null)
AND (speed_zone = cast(t_speedzone as integer) OR  t_speedzone is null)

AND dr_sex = ANY( '{Male, Female}'::text[] )
GROUP BY dr_sex