PostgreSql + Typecast of Boolean into Character Type

Question

Here, I am unable to convert boolean value into character in postgre sql query.

SELECT *FROM ltl_class_nmfc_aliases 
WHERE ltl_class_nmfc_aliases.id 
NOT IN(SELECT ltl_class_nmfc_aliases_id FROM commodities_shippeds 
WHERE commodities_shipped_obj_type LIKE 'ClientOffice') 
OR ltl_class_id IS NULL 
AND lower(commodity_description_alias) LIKE E'%%' 
AND lower(ltl_value) LIKE E'%92.5%' 
AND hazardous :: integer LIKE E  '%%' 
AND cast(schedule_b as character varying(255)) LIKE E'%%' 
AND cast(harmonized_tariff as character varying(255)) LIKE E'%%' 
ORDER BY commodity_description_alias,ltl_value LIMIT 25;

Here I am unable to typecast at AND hazardous :: integer LIKE E '%%' Suggest me how to make typecast ?

Answer 1

What about case?

(case when hazardous then 'Bad juju' else 'Ok.' end) as safety

You can use cast:

postgres=# select cast(1 as boolean);
 bool
------
 t

postgres=# select cast(0 as boolean);
 bool
------
 f

postgres=# select cast('false' as boolean);
 bool
------
 f

postgres=# select cast('False' as boolean);
 bool
------
 f

postgres=# select cast('T' as boolean);
 bool
------
 t

postgres=# select cast('F' as boolean);
 bool
------
 f

postgres=# select cast('Y' as boolean);
 bool
------
 t

postgres=# select cast('N' as boolean);
 bool
------
 f

So it could be:

 select ... where ... and hazardous = cast(:your_variable as bool)

You can also cast to varchar:

select cast(hazardous to varchar)...

But some older database connection implementations like BDE choke on this because they expect an explicit column width for varchar fields.