Forum OpenACS Q&A: stumped on a postgresql query ... using the || operator for types char and bool

1: stumped on a postgresql query ... using the || operator for types char and bool

Posted by Matthew Geddert on 10/26/02 06:18 AM

Hello, this query is stumping me. I'm using postgresql, and am not able to bind a character variable (the role field) with the boolean field public_role_p... and i figure i should just re-cast the boolean expression as char but that isn't working because it says i can't cast bool as char. Does anybody have any ideas?


select role || ' ' || public_role_p as role_details,
       CASE WHEN public_role_p = 't'
            THEN ' (public role)'
            ELSE ''
            END
          from events_organizer_roles

This is the error i get


 Unable to identify an operator '||' for types
   'character varying'and 'boolean'
    You will have to retype this query using an explicit cast

Thanks for the help.

2: Response to stumped on a postgresql query ... using the || operator for types char and bool (response to 1)

Posted by Vinod Kurup on 10/26/02 07:49 AM

Hi Matt,

PG boolean types can't be casted to other types - instead you have to use a CASE statement.

select role || ' ' || case when public_role_p then 't' else 'f' end as role_details,
...

PG Docs Reference

3: Response to stumped on a postgresql query ... using the || operator for types char and bool (response to 1)

Posted by Matthew Geddert on 10/26/02 08:03 AM

Thanks Vinod, that did it... i hadn't realized that boolean can be referenced that way in case statements... i searched the postgresql docs, but didn't find it because i was searching for the wrong things... i was looking as cast statements, and not the properties of boolean values. Ah, the joys of learning a the little things when dealing with a database I'm new to.