Postgres Update column with Enum from string

Question

I am building a migration. Changed a column from string to Enum-Type and want to insert the saved values as Enum into the table, but no idea how to do this. I inserted my values into a new table, droped the colum, created it with an enum type and got only the strings in the new table. The string have the same text as the enum. The code is:

UPDATE event_participations
    SET kind = participations_migration.kind
    FROM participations_migration
    WHERE participations_migration.part_id = event_participations.id

Participations_type is the name of the enum with values like "going", "not_going". Now I have strings in participations_migration.kind and want to get the enum value to update the event_participations.kind.

I need a subselect or join to update event_participations with the enum value according to the string which is set in participations_migration.kind. Any idea?

sticky bit · Accepted Answer · 2018-05-14 21:02:57Z

3

If the text is identical to the enum member text, a cast to the enum type should be enough.

    SET kind = participations_migration.kind::participations_type

answered May 14, 2018 at 21:02

sticky bit

37.7k12 gold badges34 silver badges46 bronze badges

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

Comments

user37649 · Accepted Answer · 2022-04-09 16:01:59Z

1

A few years later - but I found the above doesn't work. Using postgres 13.5 and datagrip to query, you must add quotations around the enum like so:

SET kind = participations_migration.kind::"participations_type"

answered Apr 9, 2022 at 16:01

user37649

745 bronze badges

Collectives™ on Stack Overflow

Postgres Update column with Enum from string

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related