Postgres cast Array to a Custom Type

Question

I have to cast a varchar array to a custom Type but it is failing.

Scenario

CREATE TYPE foo AS (foo text[]);

SELECT ARRAY['TEST_ONE']::foo; -- fails with ERROR: cannot cast type text[] to foo

I actually have to pass this type as an optional parameter to a function and hence I have to place its default value in the function parameters list. Like this

create function foo_func(par1 foo DEFAULT ARRAY['TEST_ONE']::foo) .... but this doesn't work due to above mentioned issue...

Help will be much appreciated..

Why obfuscate such a simple thing as a text array with a custom type? — user330315
– user330315, Commented Dec 2, 2019 at 19:06

klin · Accepted Answer · 2019-12-02 19:53:12Z

2

It is hard to guess why someone would want to complicate his life with such a strange idea. Anyway, the type foo is a composite type with a single text[] element, so the literal should look like:

SELECT ROW(ARRAY['TEST_ONE'])::foo;

Maybe a domain would be more handy:

create domain foo as text[];
select array['test_one']::foo;

edited Dec 2, 2019 at 19:53

answered Dec 2, 2019 at 19:17

klin

123k15 gold badges240 silver badges262 bronze badges

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

2 Comments

some_user Over a year ago

Thanks @Klin, this helps.. Its automated conversion of thousands of objects from Oracle. just picking the failed ones :)

klin Over a year ago

I see. Did you think about a domain?

Collectives™ on Stack Overflow

Postgres cast Array to a Custom Type

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related