CODEWITHSUNDEEP
c#postgresqlenumsentity-framework-core
Tomas Kubes
Tomas Kubes

Reputation: 25138

Change underlying enum value in Postgres

I have .NET Core application which communicate with a Postgres database using entity framework.

There was a enum used in model which create a Postgres enum type in postgres.

Now I need such migration that will change the enum into flags, so I need to change enum integer value in C#.

I feel I should also change the underlaying enum values in Postgres, but I don't see any numbers there, I just see only enum names in type definition.

How the Postgres stores enum internally?

How can I migrated it if integer value of enum is changed in C#?

Upvotes: 0

Views: 1056

Answers (2)

Belayer
Belayer

Reputation: 14886

This probably does not answer your question directly, but useful for understanding under the hood. Postgres stores enums with both numeric and string components. You use the string component for all references both setting and testing, references the numeric component generates an exception. Internally Postgres uses the numeric component for sorting (perhaps other uses I am not aware of). This numeric is (fyi) stored as a floating point value. Adding value(s) to an emum is a simple Alter type. Removing them cannot be done directly - it evolves deleting and recreating along with managing all dependencies. You can see the component values, both numeric and string with the query (see Demo)

select * from pg_catalog.pg_enum;

That gives you all values for all enums, unfortunately getting a specific one is more complicated.

Upvotes: 1

Stefanov.sm
Stefanov.sm

Reputation: 13049

I am not aware how Postgres stores enum internally and prefer to not rely on it.
Assuming that your enum type is called _enumtype and that column is called _enumcolumn then you can alter the table structure (enum -> integer) and keep the data in it like this:

alter table _mytable 
  alter column _enumcolumn type integer 
  using array_position(enum_range(null::_enumtype), _enumcolumn) - 1;

The enum type words will be replaced with 0, 1, 2, .... respectively.
So you are free to make whatever changes and updates you need after that.

Upvotes: 2

Related Questions