postgresql changing column type from array to integer throwing casting error

Question

I am changing the postgresql column data type from integer[] to integer, while executing below query,

alter table contact_type alter column is_delete set data type integer USING is_delete::integer

i am getting below error

ERROR:  cannot cast type integer[] to integer
LINE 1: ...umn is_delete set data type integer USING is_delete::integer
                                                              ^
SQL state: 42846
Character: 86

but when tried to change datatype from varchar[] to char, below query works fine

alter table contact_type alter column ct_type set data type varchar

i have referred this link so link but it is not working for converting array to normal data type..

Edit :- it is empty table without any data...

a_horse_with_no_name a_horse_with_no_name · Accepted Answer · 2019-12-23T08:21:45

You need to pick the array element that you want to use. You can't convert e.g. 42 integers to a single one.

E.g. if you want to use the first element of the array:

alter table contact_type  
    alter column is_delete 
    set data type integer USING is_delete[1];

But a column named is_delete should probably be a boolean rather than an integer.

postgresql changing column type from array to integer throwing casting error

1 Answers