change column datatype from array to integer

Question

I need to change the data type of a column from _float8 to int4

ALTER TABLE "table" ALTER COLUMN "col" SET DATA TYPE int4;

results in column "col" cannot be cast automatically to type integer

ALTER TABLE "table" ALTER COLUMN "col" SET DATA TYPE int4 USING (col::integer);

results in cannot cast type double precision[] to integer

any ideas?

@niga Create a new column and copy over the element of the array that should be an integer. Drop the old column and rename the new column. — Jeremy, Jun 25 '19 at 12:14

score 4 · Accepted Answer · answered Jun 25 '19 at 12:11

4

You have to point out which element of the array should be used in the conversion, e.g.

alter table x alter column y set data type int4 using (y[1]::int)

answered Jun 25 '19 at 12:11

klin

score 0 · Answer 2 · answered Jun 25 '19 at 12:11

0

The problem is that you have an array. To fix this, you need array operators:

ALTER TABLE "table"
    ALTER COLUMN "col" SET DATA TYPE int4 USING (col[1]::integer);

Here is a db<>fiddle.

answered Jun 25 '19 at 12:11

Gordon Linoff

2 Answers2