How to Alter Boolean column to Integer column in PostgreSQL

Database
Dec 24 2018
786 Views

How to Alter Boolean column to Integer column in PostgreSQL

I faced a situation where I had to change a column type from Boolean to Integer. And I wanted to rename the column name. For example, we have table that has a boolean column "bDeleted". I have to remane "dDeleted" to "iDeleted" and all boolean values need to convert into 0 and 1. Then you have to run below PostgreSQL queries.

Queries

ALTER TABLE users ALTER "bDeleted" SET DEFAULT null;

ALTER TABLE users
ALTER "bDeleted" TYPE INTEGER
USING
CASE
WHEN 'f' THEN 0 ELSE 1
END;

ALTER TABLE users RENAME "bDeleted" TO "iDeleted";

ALTER TABLE users ALTER "iDeleted" SET DEFAULT 0;

Leave a Reply