Обсуждение: Cast character to boolean

I'm currently refactoring a database that somebody else designed.
When the database was designed he used character columns with a length
of 1 char to represent some values that really should have been
represented as booleans.  He used 'y' for true and 'n' for false.

I want to cast these columns into the correct type, because you could
in theory set the columns in question to any single character value.
I don't seem to be able to do so, however, the database keeps claiming
that the cast cannot be done.

I tried casting the columns in question to character varying and then
changing all the 'y's to 'TRUE's, and all the 'n's to 'FALSE's. This
wasn't a problem.  But casting from this format to boolean still gives
an error.

Does anybody know how to do this?

A case statement will work just fine:

select case when sub.col1 = 'y' then true else false end as
col1_boolean, sub.col1 from (select cast('y' as varchar) as col1) sub



Jon
> -----Original Message-----
> From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-
> owner@postgresql.org] On Behalf Of Gordon
> Sent: Tuesday, March 18, 2008 10:18 AM
> To: pgsql-general@postgresql.org
> Subject: [GENERAL] Cast character to boolean
>
> I'm currently refactoring a database that somebody else designed.
> When the database was designed he used character columns with a length
> of 1 char to represent some values that really should have been
> represented as booleans.  He used 'y' for true and 'n' for false.
>
> I want to cast these columns into the correct type, because you could
> in theory set the columns in question to any single character value.
> I don't seem to be able to do so, however, the database keeps claiming
> that the cast cannot be done.
>
> I tried casting the columns in question to character varying and then
> changing all the 'y's to 'TRUE's, and all the 'n's to 'FALSE's. This
> wasn't a problem.  But casting from this format to boolean still gives
> an error.
>
> Does anybody know how to do this?
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general

Roberts, Jon wrote:
> A case statement will work just fine:
>
> select case when sub.col1 = 'y' then true else false end as
> col1_boolean, sub.col1 from (select cast('y' as varchar) as col1) sub

Be aware of the danger here.  What happens if col1 is NULL?