Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Postgres Alter Column Integer to Boolean

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.

like image 871
samsina Avatar asked Nov 16 '09 05:11

samsina


People also ask

How do I change the datatype of a column in PostgreSQL?

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.

How do I change the constraint of a column in PostgreSQL?

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 do I change the datatype size in PostgreSQL?

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.

What is the difference between Bool and Boolean in PostgreSQL?

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.


1 Answers

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.

like image 162
catchdave Avatar answered Oct 06 '22 17:10

catchdave