1

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

pappu_kutty
  • 2,378
  • 8
  • 49
  • 93

1 Answers1

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.

  • i dont have any rows.. it is empty table with out any data – pappu_kutty Dec 23 '19 at 09:22
  • but above alter statement works, but it is not working for empty table without any rows – pappu_kutty Dec 23 '19 at 09:26
  • is_delete is integer of value 0 & 1, i dont want any charatects defining the boolean – pappu_kutty Dec 23 '19 at 09:28
  • a `boolean` is **not** "characters" - it's, well, a boolean. The statement will work just fine on an empty table: https://dbfiddle.uk/?rdbms=postgres_12&fiddle=704e4c45d9fd6d5ea3961b332c758835 –  Dec 23 '19 at 09:40
  • 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.. – pappu_kutty Dec 23 '19 at 10:03
  • it takes the first element of a null array which in turn yields null which makes sense on an empty table –  Dec 23 '19 at 10:11