0

I'm trying to copy the enum values between two columns in a table. The the two enum types have the same enum values:

UPDATE dogs SET breed = breed_old;
...
ERROR:  column "breed" is of type "breed" but expression is of type "breed_old"

I've also tried:

UPDATE dogs SET breed = breed_old::text;
...
ERROR:  column "breed" is of type "breed" but expression is of type text

Any help would be appreciated.

tom_nb_ny
  • 110
  • 10

1 Answers1

2
create type foo as enum ('a','b');
create type bar as enum ('a','b');

select 'a'::foo;
┌─────┐
│ foo │
├─────┤
│ a   │
└─────┘

select 'a'::foo::text;
┌──────┐
│ text │
├──────┤
│ a    │
└──────┘

select 'a'::foo::text::bar;
┌─────┐
│ bar │
├─────┤
│ a   │
└─────┘

Or probably more convenient:

update dogs set
    breed = case breed_old
        when 'val1' then 'val1'::breed
        when 'val2' then 'val2'::breed
        ...
    end;
Abelisto
  • 14,826
  • 2
  • 33
  • 41
  • Yikes, it's that complicated? I thought there was just a way to cast the old column in a way that the new column accepts it, since I can insert plain text enum values into either column. – tom_nb_ny Dec 31 '20 at 16:56
  • @tom_nb_ny PostgreSQL is the [strong typed](https://en.wikipedia.org/wiki/Strong_and_weak_typing) database. – Abelisto Dec 31 '20 at 17:33
  • I realize that but logically if I can run `INSERT INTO table (type) VALUES 'value';` but cannot run `UPDATE table SET type = type_old::text WHERE id=1;` there is a discrepancy or unsupported logic in Postgres. Moreover this whole conundrum came about because I can't simply delete enum values from an enum type. Changing the structure of an enum type is a half hour excursion of cryptic SQL soup compared to a few seconds in MySQL. Sorry - had to vent about Postgres. Thanks for your help. – tom_nb_ny Dec 31 '20 at 17:39
  • @tom_nb_ny In the PostgreSQL something between quotes is not the text actually but depends on the context. Probably the most simple example: `select 1 + '2';` returns `3` because the context was determined by the `1` value and by the `+` operator and `'2'` was implicitly converted to the `integer` accordingly. Same here: in the `INSERT INTO table (type) VALUES 'value';` expression `'value'` was implicitly converted to the type of `type` column but `type_old::text` expression explicitly converts the value to the `text` type which is incompatible with the target. – Abelisto Jan 10 '21 at 01:09