Re: Cast character to boolean

On Mar 18, 9:18 am, Gordon <gordon.mc...@ntlworld.com> wrote:
> 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?

Have yout tried
ALTER TABLE foo ALTER col TYPE boolean USING CASE WHEN col = 'y' THEN
true WHEN column = 'n' then FALSE END;