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

Question

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

S-Man · Accepted Answer · 2021-02-26 11:11:34Z

1

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
        ]
);

answered Feb 26, 2021 at 11:11

S-Man

24k9 gold badges51 silver badges78 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related