Re: Cast character to boolean

On Mar 19, 5:52 pm, elba(dot)(dot)(dot)(at)gmail(dot)com wrote:
> 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;

I did find a solution in the end but it was nothing like as elegant as
yours. In the end I created two new boolean columns, updated the
values in the new columns depending on the values in the old columns,
dropped the old columns and renamed the new boolean columns to the
names of the deleted columns. As you cn imagine, not a fun
procedure. I'll keep this post bookmarked though if I ever have to do
anything like that again.