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;

On Mar 19, 5:52 pm, elba...@gmail.com wrote:

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;

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.

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

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?