I want to convert the datatype of a column in a table from text to boolean. The data in the column is 't' for true and 'f' for false.
I tried to convert it with this statement:
ALTER TABLE public.checktypes ALTER COLUMN deleted SET DATA TYPE boolean USING deleted::boolean;
This works for the conversion of the data type just fine. The problem is, all of them are now false. How can I accomplish that, if the value before the conversion is 't', the boolean will set to true?
ALTER TABLE mytabe ALTER COLUMN mycolumn DROP DEFAULT;
ALTER TABLE mytabe ALTER mycolumn TYPE bool USING CASE WHEN mycolumn=0 THEN FALSE ELSE TRUE END;
ALTER TABLE mytabe ALTER COLUMN mycolumn SET DEFAULT FALSE;
First:
update checktypes
set deleted = case deleted when 't' then '1' when 'f' then '0' end
then:
ALTER TABLE public.checktypes ALTER COLUMN deleted SET DATA TYPE boolean USING deleted::boolean;
you can try this way: create temp table, insert and convert text to boolean to temp table, drop old column, add new column of boolean type and invoke update
show this code:
create table _temp
(
id integer,
val_boolean boolean
)
insert into _temp(id,value) select id,val::boolean from your_table;
alter table your_table drop column val;
alter table your_table add column val boolean;
update your_table t set val = (select val_boolean from _temp te where te.id = t.id);
