0

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?

MichaelCorSibin
  • 1
  • You will need to show the table definition where you use and are trying to alter the type. At a guess you have declared the column to be an array of `myschema.test`. – Adrian Klaver Jun 24 '22 at 17:57
  • Thanks for your reply . This is my table structure. CREATE TABLE myschema1.table ( id text NOT NULL, resource text NOT NULL, testcolumn myschema.test ); – MichaelCorSibin Jun 25 '22 at 04:20
  • Your schema is already broken. Don't use composite types in table definitions and don't use arrays. – Laurenz Albe Jun 25 '22 at 07:12
  • 1) The table definition you show is not for the one you are doing the type change on. That table has `testcolumn` defined as an array `myschema.test[]`. 2) My testing indicated you cannot `CAST` from one composite type to another so the `USING` is not going to work in any case. 3) See @LaurenzAlbe's comment an rethink your design. – Adrian Klaver Jun 25 '22 at 14:58

0 Answers0