How to change/alter the data type of a column from a table which has a custom data type in POSTGRESQL?

132 views Asked by At

I have a custom datatype as below.

CREATE TYPE myschema.test AS (
        id text,
        event text,
        severity text,
        status text,
        value text,
        text text,
        type text,
        update_time timestamp without time zone
    );

I am using this datatype in one of the tables. Now I want to change the schema of this data type and create it as myschema1.Test and alter the column of the table with new data type created in myschema1. But I am getting an error as below even if both the data types are having the same structure.

alter table if exists myschema1.table 
   alter column testcolumn type myschema1.test;
ERROR:  column "testcolumn" cannot be cast automatically to type myschema1.test
HINT:  You might need to specify "USING testcolumn::myschema1.test".

When I tried using cast operator as per the hint facing the below error.

ERROR:  cannot cast type myschema.test[] to myschema1.test
LINE 1: ...test type myschema1.test using testcolumn::myschema1...

How to handle this one?

0

There are 0 answers