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...

user330315 · Accepted Answer · 2019-12-23 08:21:45Z

1

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.

answered Dec 23, 2019 at 8:21

user330315

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

6 Comments

pappu_kutty Over a year ago

i dont have any rows.. it is empty table with out any data

pappu_kutty Over a year ago

but above alter statement works, but it is not working for empty table without any rows

pappu_kutty Over a year ago

is_delete is integer of value 0 & 1, i dont want any charatects defining the boolean

user330315 Over a year ago

a boolean is not "characters" - it's, well, a boolean. The statement will work just fine on an empty table: dbfiddle.uk/…

pappu_kutty Over a year ago

but in the documentation, using "USING is_delete::integer" to cast integer[] to integer type, but it doesnt work, where as is_delete[1] doenst make sense, if the table is empty..

|

Collectives™ on Stack Overflow

postgresql changing column type from array to integer throwing casting error

1 Answer 1

6 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Linked

Related