postgresqluser-defined-typescomposite-types

Postgres insert an array of an user-defined type with an array inside


I created this type for length:

CREATE TYPE length AS (value numeric, uom text );

Then I created a type that is an array of length

CREATE TYPE test_type AS (comp_height length[]);

After this, I created a table with a column of type test_type array

CREATE TABLE test_table (test_column test_type[]);

Now I can not insert into this table. I have tried this way

insert into test_table (test_column)
values (
    ARRAY[
          ARRAY[(1,'m')::length,
                (20,'m')::length],
          ARRAY[(3,'m')::length,
                (40,'m')::length]
         ]::test_type
        );

but I get a cast error :(

ERROR: cannot cast type length[] to test_type

I appreciate any help, thanks


Solution

  • demo:db<>fiddle

    Your have to convert the length[] into a record using ROW(), which can be cast into your new test_type

    insert into test_table (test_column)
    values (
            ARRAY[   
              ROW(ARRAY[(1,'m')::length, (20,'m')::length])::test_type,
              ROW(ARRAY[(3,'m')::length, (40,'m')::length])::test_type
            ]
    );