Our database schema was designed before postgresql supported arrays and
contains a custom type which is basically a float4 array. I would like
to clean things up and convert the custom datatype to float4[], as this
would obviate the need for us to compile a custom shared object. We are
hitting problems with pg8.3 and I would rather just drop the custom stuff.
Problem is, I cannot do an ALTER COLUMN:
mydb=# alter table mytable alter column mycolumn float4[];
ERROR: column "mycolumn" cannot be cast to type "float4[]"
Any ideas on how to get around this, other than writing code to read
data from every row and copy it out to a new table? The data looks like
this:
mydb=# select mycolumn from mytable limit 4;
mycolumn
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
[30.9100000,12.3000000]
[5.9500000,15.7800000,1.5800000,1.0700000,1.0500000,0.9400000,1.7500000,7.8800000]
[10.6800000,29.0300000]
[15.2500000,32.8800000]
Mike