Search Postgresql Archives

Case statement with different data types

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux