0

Given an enum type (e.g. letter).

What is the syntax to convert an existing array to the enum type? The existing values are valid for the enum. For example, how would I convert letter_data in:

create table sample (
 letter_data text[];
);

To the letter enum?

1 Answer 1

3

To convert an existing text[] array to the enum type letter[] (like you ask) simply cast:

SELECT '{b,c,a}'::text[]::letter[];

To actually convert the table column (like you probably wanted to ask):

ALTER TABLE sample ALTER letter_data TYPE letter[] USING letter_data::letter[];

Triggers a whole-table rewrite. Only some noted exceptions allow in-place conversion. See:

We need the explicit cast in a USING clause since, quoting the manual for ALTER TABLE:

A USING clause must be provided if there is no implicit or assignment cast from old to new type.

fiddle

Aside: I rarely use enum types. There is often a better solution.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.