From: | Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> |
---|---|
To: | Gus Waddell <angus(dot)waddell(at)palcare(dot)com(dot)au> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Case statement with different data types |
Date: | 2009-10-14 09:20:28 |
Message-ID: | 162867790910140220r7bcfd05dv10479eb6d256a0f3@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Hello
try to explicit cast
select 't'::boolean;
regards
Pavel Stehule
2009/10/14 Gus Waddell <angus(dot)waddell(at)palcare(dot)com(dot)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
>
>
From | Date | Subject | |
---|---|---|---|
Next Message | Sam Mason | 2009-10-14 10:28:23 | Re: Procedure for feature requests? |
Previous Message | Scott Marlowe | 2009-10-14 08:02:13 | Re: Cannot start the postgres service |