Search code examples
arrayspostgresqlddl

change column datatype from array to integer

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?

Solution

  • 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)

    db<>fiddle.