I migrated my Django website database from MySQL to PostgreSQL using a third party tool pgloader. But since MySQL stores BOOLEAN data as TINYINT by default(at least with Django), it got converted to SMALLINT in PostgreSQL. Now Django is showing error that smallint is being treated as boolean. Due to this, I want to convert all smallint columns to boolean. Is there a single command to convert all columns in all tables in a single database to required type? If I have to perform the operation separately for each table, that's OK too.
EDIT:
Column structure:
public | smallint | default '1'::smallint
Here public is column name, smallint it's type and has it default value of '1'.
The code I used:
utkarsh@utkarsh-Lenovo-G580:~$ psql -d thakurani -U utkarsh
Password for user utkarsh:
psql (9.6.3)
Type "help" for help.
thakurani=# alter table topics_topic alter COLUMN public type boolean using(public::text::boolean); ERROR: default for column "public" cannot be cast automatically to type boolean
thakurani=# alter table topics_topic alter COLUMN public type text using(public::text);
ALTER TABLE
thakurani=# alter table topics_topic alter COLUMN public type boolean using(public::boolean);
ERROR: default for column "public" cannot be cast automatically to type boolean
thakurani=# ALTER TABLE topics_topic ALTER COLUMN public TYPE boolean USING CASE WHEN public = '0' THEN FALSE WHEN public = '1' THEN TRUE END;
ERROR: default for column "public" cannot be cast automatically to type boolean
thakurani=#