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 ?

Solution

How about using a case statement?

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

You can also use the cast statement:

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)...

Some database driver implementations (like BDE from Borland) choke on this because they expect an explicit column width for varchar fields.

if you are just filtering for hazardous=true, use just "AND hazardous".