Thread: Case statement with different data types

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 <font
 color="#ff0000">'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

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