Thread: convert custom datatype to array

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

Mike Charnoky wrote:
> 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[]"

You can specify a USING clause to let the system know exactly how to
convert your type to float[].  I suspect you can use float4in() and
something to read out your type into the format that float4in expects (a
hacked up version of your out function perhaps).

--
Alvaro Herrera                  http://www.amazon.com/gp/registry/5ZYLFMCVHXC
"La tristeza es un muro entre dos jardines" (Khalil Gibran)

OK, forgive my ignorance here, but the maintainer of our custom data
type code is no longer with us and this is new territory for me.  We do
have a function which takes our custom data type and returns a cstring.
 Is there a pg function which converts a cstring to text type?  This
seems to be the missing link to eventually get the data to float4[].


Mike

Alvaro Herrera wrote:
> Mike Charnoky wrote:
>> 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[]"
>
> You can specify a USING clause to let the system know exactly how to
> convert your type to float[].  I suspect you can use float4in() and
> something to read out your type into the format that float4in expects (a
> hacked up version of your out function perhaps).
>

Mike Charnoky wrote:
> OK, forgive my ignorance here, but the maintainer of our custom data
> type code is no longer with us and this is new territory for me.  We do
> have a function which takes our custom data type and returns a cstring.
>  Is there a pg function which converts a cstring to text type?  This
> seems to be the missing link to eventually get the data to float4[].

Sure, textin() does that.

--
Alvaro Herrera                 http://www.amazon.com/gp/registry/DXLWNGRJD34J
"Coge la flor que hoy nace alegre, ufana. ¿Quién sabe si nacera otra mañana?"

Thanks!  That did the trick.  For posterity, I was able to do the final
conversion using:

alter table mytable alter column mycolumn type float4[] using
string_to_array(trim(both '[]' from
textin(nbf4a_out(mycolumn))),',')::float4[];


Mike

Alvaro Herrera wrote:
> Mike Charnoky wrote:
>> OK, forgive my ignorance here, but the maintainer of our custom data
>> type code is no longer with us and this is new territory for me.  We do
>> have a function which takes our custom data type and returns a cstring.
>>  Is there a pg function which converts a cstring to text type?  This
>> seems to be the missing link to eventually get the data to float4[].
>
> Sure, textin() does that.
>