Convert PostgreSQL array type

Question

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?

Answer 1

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:

• ALTER timestamptz column to timestamp without "converting" data?

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.