postgres converting text to ENUM when TEXT values don't match

Question

I'm trying to convert a column from text to enum value where the text values don't match the new enums. Is this possible with Postgres without having to drop the column first or write a bunch of update scripts prior?

CREATE TABLE "test_table" (
    "id" uuid NOT NULL DEFAULT uuid_generate_v4(),
    "shape" text
);

insert into test_table(shape) values ('Round');
insert into test_table(shape) values ('Square');

CREATE TYPE "public"."test_table_shape_enum" AS ENUM(
    'round',
    'square'
    );

ALTER TABLE test_table
    ALTER shape TYPE test_table_shape_enum USING shape::test_table_shape_enum;

[22P02] ERROR: invalid input value for enum test_table_shape_enum: "Round"

A few ways I see doing this is either do an update to all rows prior to doing the alter.

update test_table set shape='round' where shape='Round';
update test_table set shape='square' where shape='Square';

But it would be nice if I can do it with all one go; is there a better way of doing this without having to write a bunch of update scripts prior to running the alter command?

klin · Accepted Answer · 2022-06-02 17:47:53Z

2

If the only problem is the letter case, use lower():

ALTER TABLE test_table
    ALTER shape TYPE test_table_shape_enum 
    USING lower(shape)::test_table_shape_enum;

Test it in Db<>Fiddle.

answered Jun 2, 2022 at 17:47

klin

123k15 gold badges241 silver badges263 bronze badges

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

1 Comment

Garuuk Over a year ago

This is great since we have hundreds of custom shapes. Thank you.

Collectives™ on Stack Overflow

postgres converting text to ENUM when TEXT values don't match

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related