Postgres - How to cast an array of enum_1 to enum_2?

Question

I'm trying to modify the values of an enum in my schema ("feature" in the below example).

I'm trying to do this by renaming the old enum and introducing a new one that has the values I want, and then altering the table definition to the new enum.

I'm following this blog post here: https://blog.yo1.dog/updating-enum-values-in-postgresql-the-safe-and-easy-way/. But instead of the column being a simple column of the enum, my column is actually an array of the enum.

When I try to run the alter table statement in the below statements, I get the error:

[42804] ERROR: column "features" is of type feature_old[] but expression is of type feature_v2[] Hint: You will need to rewrite or cast the expression.

alter type feature rename to feature_old;

create type feature_v2 as enum (
  'enable_create_keyword',
  'enable_make_payment',
  'enable_test_data_flags'
);

-- ... cleanup of column array values to be compatible with new enum ...

alter table app_user alter column features type feature_v2
using features::feature_old[]::feature_v2[];

drop type feature_old;

But, I'm lost - what should the cast expression look like?

Postgres version is 9.6

EDIT

This is the relevant part of the previous version's schema DDL for the feature enum and app_user table that was requested by @VaoTsun.

-- feature enum and column

create type feature as enum ('enable_create_keyword', 'enable_make_payment');
comment on type feature is 
  'if default functionality is disabled feature name starts with enable_, if default is enabled starts with disable_'
;

alter table app_user add column 
features feature[] not null default ARRAY[]::feature[];


-- feature data
update app_user
set features = ARRAY['enable_create_keyword', 'enable_make_payment']::feature[]
where email = '[email protected]';

update app_user
set features = ARRAY['enable_create_keyword']::feature[]
where email = '[email protected]';

Shorn · Accepted Answer · 2020-11-26 23:12:37Z

5

Thanks to both Vao Tsun and Nick Barnes; this is the code that appears to work for me. I have marked Vao Tsun's answer as correct. Any answers that provide a more concise version would be gratefully upvoted.

alter type feature rename to feature_old;

create type feature_v2 as enum (
  'enable_create_keyword',
  'enable_make_payment',
  'enable_test_data_flags'
);

alter table app_user alter column features drop default ;

alter table app_user alter column features type feature_v2[]
using features::feature_old[]::text[]::feature_v2[];

alter table app_user alter column features set default ARRAY[]::feature_v2[];

drop type feature_old;

edited Nov 26, 2020 at 23:12

answered Jan 29, 2018 at 9:37

Shorn

22.1k18 gold badges110 silver badges209 bronze badges

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

Comments

Vao Tsun · Accepted Answer · 2018-01-29 10:03:41Z

2

with assumption that old enum has same values, but less, you should be able to simply cast it's value to text and then to a v2:

try this:

t=# create or replace function feature2v2(feature_old) returns feature_v2 as
$$
select $1::text::feature_v2;
$$
language sql strict;
CREATE FUNCTION
t=# create cast (feature_old AS feature_v2) WITH FUNCTION feature2v2(feature_old) AS ASSIGNMENT;
CREATE CAST

gives me:

t=# alter table app_user alter column features type feature_v2[]
using features::feature_v2[];
ALTER TABLE
t=# \d+ app_user
                                            Table "postgres.app_user"
  Column  |     Type     | Collation | Nullable |        Default         | Storage  | Stats target | Description
----------+--------------+-----------+----------+------------------------+----------+--------------+-------------
 email    | text         |           |          |                        | extended |              |
 features | feature_v2[] |           | not null | ARRAY[]::feature_old[] | extended |              |

t=# \dT+ feature_v2
                                                 List of data types
  Schema  |    Name    | Internal name | Size |        Elements        |  Owner   | Access privileges | Description
----------+------------+---------------+------+------------------------+----------+-------------------+-------------
 postgres | feature_v2 | feature_v2    | 4    | enable_create_keyword +| postgres |                   |
          |            |               |      | enable_make_payment   +|          |                   |
          |            |               |      | enable_test_data_flags |          |                   |
(1 row)

which looks like what you expect

UPDATE

catching up with Nick Barnes comments - creating a cast here is overhead and leaves bad defualt for column, the right approach her is:

alter table app_user alter column features drop default;
alter table app_user alter column features type feature_v2[] using features::feature_old[]::text[]::feature_v2[];
alter table app_user alter column features set default ARRAY[]::feature_v2[];

Leaving the previous version untouched to demonstrate the bad approach with hints how it is bad

edited Jan 29, 2018 at 10:03

answered Jan 29, 2018 at 8:52

Vao Tsun

52.4k13 gold badges114 silver badges149 bronze badges

8 Comments

Nick Barnes Over a year ago

If you're creating a cast, why keep the using clause? If you have a using clause, why not put the conversion in there and do away with the cast altogether?

Vao Tsun Over a year ago

ah. I see. because I create a not implicit cast. and I can't just cast feature_old::text::feature_v2 becase the OP uses array. Am I missing something obvious?..

Nick Barnes Over a year ago

You can use feature_old[]::text[]::feature_v2[]. The bigger problem with the cast is that it's allowed Postgres to automatically change the column default; as you can see from your \d+ output, the default still references feature_old, which will prevent the type from being dropped.

Shorn Over a year ago

@NickBarnes Are you saying that the alter statement using clause should be "using feature_old[]::text[]::feature_v2[]"? Becuase that doesn't work for me, the connection just dies (no error).

Vao Tsun Over a year ago

@NickBarnes great point - thank you! I could not simply cast it though rextester.com/XXVJLN61201, but I will definetely elaborate the answer for at least default value

|

Collectives™ on Stack Overflow

Postgres - How to cast an array of enum_1 to enum_2?

2 Answers 2

Comments

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related