Can I make the following query shorter and/or optimal?

WITH myvars (t_state, t_hv_involved, num_vehicle_hv )
AS (VALUES ('TAS', null, null))

SELECT * FROM safety.crash_summary_view c, myvars 
WHERE jurisdiction = t_state
AND ((t_hv_involved::boolean = TRUE AND c.num_vehicle_hv > 0)
   OR t_hv_involved is null
   OR t_hv_involved::boolean = FALSE)

If t_hv_involved is true then it should filter on num_vehicle_hv > 0.
If t_hv_involved is false or null, do not filter.

CodePudding user response：

...
AND   (t_hv_involved IS NOT TRUE OR c.num_vehicle_hv > 0)

Assuming t_hv_involved is type boolean as it should, you don't need to cast.

The manual:

boolean IS NOT TRUE → boolean

Test whether boolean expression yields false or unknown.
true IS NOT TRUE → f
NULL::boolean IS NOT TRUE → t (rather than NULL)

The complete test case could be:

SELECT *  -- you really need all columns?
FROM   safety.crash_summary_view c
CROSS  JOIN (
   VALUES
     ('TAS', null::bool, null::numeric)
   ) v (t_state, t_hv_involved, num_vehicle_hv)
WHERE  c.jurisdiction = v.t_state
AND   (v.t_hv_involved IS NOT TRUE OR c.num_vehicle_hv > 0);

Note the explicit type declaration in the first row of the VALUES expression. I didn't cast 'TAS' as that defaults to type text anyway. You may want to be explicit and cast that, too.
Additional rows can be untyped literals - but boolean values can't be quoted.
See:

• Casting NULL type when updating multiple rows

Of course, c.num_vehicle_hv > 0 is null while you pass null for num_vehicle_hv, and the test returns no rows with v.t_hv_involved IS TRUE.