0

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.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
Samra
  • 1,815
  • 4
  • 35
  • 71
  • You can shorten it by leaving out `= TRUE` and changing `t_hv_involved::boolean = FALSE` to `NOT t_hv_involved::boolean`, but I don't think that's what you're after. – Deepstop Oct 24 '22 at 01:58

1 Answers1

1
...
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 TRUEboolean

Test whether boolean expression yields false or unknown.
true IS NOT TRUEf
NULL::boolean IS NOT TRUEt (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:

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.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228