I've a field that is INTEGER NOT NULL DEFAULT 0 and I need to change that to bool.
This is what I am using:
ALTER TABLE mytabe ALTER mycolumn TYPE bool USING CASE WHEN 0 THEN FALSE ELSE TRUE END;
But I am getting:
ERROR: argument of CASE/WHEN must be type boolean, not type integer ********** Error ********** ERROR: argument of CASE/WHEN must be type boolean, not type integer SQL state: 42804
Any idea?
Thanks.
First, specify the name of the table to which the column you want to change belongs in the ALTER TABLE clause. Second, give the name of column whose data type will be changed in the ALTER COLUMN clause. Third, provide the new data type for the column after the TYPE keyword.
The PostgreSQL ALTER TABLE command is used to add, delete or modify columns in an existing table. You would also use ALTER TABLE command to add and drop various constraints on an existing table.
How to increase the length of a character varying datatype in Postgres without data loss. Run the following command: alter table TABLE_NAME alter column COLUMN_NAME type character varying(120); This will extend the character varying column field size to 120.
PostgreSQL Boolean is a simple data type that we have used to represent only the structure of true or false data or values. PostgreSQL will support the SQL99 defined Boolean data type of SQL standard; Boolean is also known as “bool”, bool is an alias of Boolean data type in PostgreSQL.
Try this:
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;
You need to remove the constraint first (as its not a boolean), and secondly your CASE
statement was syntactically wrong.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With