Why PostgreSQL save the enums in the database as the integers but when

It was decided it's more effective this way, I guess. Enums are always strings in the protocol. They are specified in INSERT, searched in WHERE and returned from SELECT as strings.

You could say that they are stored on disk very similary to fixed-width binary integer values by careful examination of the Postgres on-disk table and index format -- but that's because the authors can do it.

And they are consistently stored internally as such, and transferred to some internal C functions that can operate with such a representation. And that's, definitely, because the Postgres can do it. It can store such values as a fixed-width bitfield, that fits in one CPU register. And nevertheless present that as strings to anyone with a straightforward conversion.

1 Reply

Know the answer? Post it — somebody with the same question will find it here.

Rajanikant Hawaldar5y ago
Accepted answer
It was decided it's more effective this way, I guess. Enums are always strings in the protocol. They are specified in INSERT, searched in WHERE and returned from SELECT as strings.

You could say that they are stored on disk very similary to fixed-width binary integer values by careful examination of the Postgres on-disk table and index format -- but that's because the authors can do it.
And they are consistently stored internally as such, and transferred to some internal C functions that can operate with such a representation. And that's, definitely, because the Postgres can do it. It can store such values as a fixed-width bitfield, that fits in one CPU register. And nevertheless present that as strings to anyone with a straightforward conversion.