How to Rename and Change Boolean column to Integer in PostgreSQL

Today, I faced a situation where I had to change a column type from Boolean to Integer. Also, I wanted to rename the column. Let’s suppose, we have tasks table that has a boolean column done. I want to rename done to status and change all false values to 0 and all true values to 1. To do that, you have to run following SQL query using PostgreSQL client.

ALTER TABLE tasks ALTER done SET DEFAULT null;
ALTER TABLE tasks
ALTER done TYPE INTEGER
USING
CASE
WHEN f THEN 0 ELSE 1
END;
ALTER TABLE tasks RENAME done TO status;
Advertisements