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 ?

Paulo Scardine · Accepted Answer · 2015-03-10 20:23:44Z

9

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

edited Mar 10, 2015 at 20:23

answered Oct 14, 2010 at 12:19

Paulo Scardine

78.2k12 gold badges134 silver badges153 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

RahulOnRails Over a year ago

I am trying case at my end but i am not getting any success. I am going to try your code now.

RahulOnRails Over a year ago

SELECT * FROM ltl_class_nmfc_aliases LEFT OUTER JOIN ltl_class_nmfcs ON ltl_class_nmfcs.id=ltl_class_nmfc_aliases.ltl_class_nmfc_id LEFT OUTER JOIN ltl_classes ON ltl_classes.id=ltl_class_nmfcs.ltl_class_id 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 case when hazardous then 'True' else 'False' end AND cast(schedule_b as character varying(255)) LIKE E'%%';

RahulOnRails Over a year ago

ERROR: argument of AND must be type boolean, not type text

Paulo Scardine Over a year ago

in the where clause use "AND (case when hazardous then 'True' else 'False' end)='True'"

Paulo Scardine Over a year ago

if you are not comparing "hazadous" with something, just filtering for value=true, the correct test is just "AND hazardous"

Collectives™ on Stack Overflow

PostgreSql + Typecast of Boolean into Character Type

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related