Re: Cast character to boolean

On Mar 18, 9:18 am, Gordon <gordon(dot)mc(dot)(dot)(dot)(at)ntlworld(dot)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;