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

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?

CodePudding user response：

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.