convert custom datatype to array

From: Mike Charnoky <noky(at)nextbus(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: convert custom datatype to array
Date: 2007-11-19 14:01:52
Message-ID: 47419750.1000808@nextbus.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Josh Harrison 2007-11-19 14:44:38 postgresql storage and performance questions
Previous Message Ian Barwick 2007-11-19 13:44:41 Re: tsearch2 best practices