Not able to alter column to integer or boolean in postgres

Question

I tried to alter a column to integer type using the below query postgres.

alter table pm_user alter column testing2 type integer using testing2::integer;

pm_user - table name
testing2 - column name

The above column would be any kind of data type eg: boolean, text, varchar(256).

It gives error as 'ERROR: invalid input syntax for integer: "NULL"' I tried solutions as below based on the previous queries asked in this website but it is not working for me.

alter table pm_user alter column testing2 type integer using (testing2::integer);                                   
alter table pm_user alter column testing2 type integer;            
alter table pm_user alter column testing2 type numeric using (testing2::numeric);         
alter table pm_user alter column testing2 type numeric (10,2);

What is the actual problem? Where it points input as null? which one is taken as null? What solution I can provide. The same query works when I try to change as

alter table pm_user alter column testing2 type text using testing2::text;  
alter table pm_user alter column testing2 type varchar(256)using testing2::varchar(256);

It is also not working for boolean.

I need to set the default value for integer column as 0 post altering data type to integer. For that purpose, I used the query "alter table pm_user alter column testing2 set default 0;" The above functionality is required for changing the data type of the column dynamically via the application for the requirement. — Manushi
– Manushi, Commented Apr 7, 2016 at 6:08

Kamil Kiełczewski · Accepted Answer · 2018-03-08 18:35:41Z

4

The error message indicates that your column contains the string literal 'NULL' instead of a "real" null value. Something like this:

create table pm_user (testing2 varchar);
insert into pm_user (testing2) values ('NULL');

(Note the 'NULL' - that is not the same as NULL)

So the expression to convert the string to a number must take care of that.

The following will convert a varchar column to integer. Any value in that column that is not a proper integer, will be changed to null:

alter table pm_user alter column testing2 type integer 
   using case when testing2 ~ '^[-+0-9]+$' then testing2::integer else null end;

~ is Postgres' operator for a regular expressions. testing2 ~ '^[0-9]+$' tests if the column only contains numbers.

edited Mar 8, 2018 at 18:35

Kamil Kiełczewski

93.7k34 gold badges401 silver badges375 bronze badges

answered Apr 7, 2016 at 6:14

user330315

Sign up to request clarification or add additional context in comments.

2 Comments

Manushi Over a year ago

This query works but I checked the column and the data in the column is empty for all rows. Why does it shows 'null' then?

Manushi Over a year ago

What I can do for boolean? Should I set to false or true as in the previous query?

Collectives™ on Stack Overflow

Not able to alter column to integer or boolean in postgres

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related