Re: Case statement with different data types

Hello

try to explicit cast

select 't'::boolean;

regards
Pavel Stehule

2009/10/14 Gus Waddell <angus.waddell@palcare.com.au>:
> Hi everyone,
>
> I am currently working on an app that is split into several databases with
> the same table but a column with differing data type.
>
> eg. Database 1
> tablename: gp
> column:  available - data type boolean
>
> Database 2
> tablename: gp
> column:  available - data type character(1)
>
> I would like to be able to create a query that returns 'T' or 'F' using the
> same code & query.
>
> I have tried a few different cast() options without success - and then
> tried:
>
> SELECT
>     CASE
>         WHEN ((SELECT data_type FROM information_schema.columns WHERE
> table_name = 'gp' and column_name = 'available') = 'boolean') THEN
>             CASE
>                 WHEN (available) THEN 'T'
>                 ELSE 'F'
>             END
>         ELSE
>             CASE
>                 WHEN (available='T' OR available='t') then 'T'
>                 ELSE 'F'
>             END
>     END as available
> FROM
>     gp
>
>
> When I run this query on the boolean data type database it works correctly -
> however on the character field I get the error 'ERROR:  argument of
> CASE/WHEN must be type boolean, not type character'
>
> I really don't want to go through and change data types just at the moment -
> can anyone advise any way around this?
>
> Many thanks,
> Gus
>
>