How to change a boolean column to bit varying in PGSQL-CodePudding

create table test_div1( i boolean)
    insert into test_div1 values('1');

ALTER TABLE test_div1 ALTER i TYPE bit varying  USING (i::text::bit varying );

//not working getting error. please help

CodePudding user response：

I think you are getting error: "t" is not a valid binary digit, postgres couldn't automatically cast true to 1

what you need to do is handle the conversion yourself like this

create table test_div1( i boolean)

insert into test_div1 values('1');

insert into test_div1 values(false), ('1'), (true), ('0');

ALTER TABLE test_div1 ALTER i TYPE bit varying  USING (case when i = true then B'1' else B'0' end);

here is a working example https://www.db-fiddle.com/f/wukNuibgNsJqzydNqz4eFy/0