On Sat, 2017-09-02 at 18:06 -0400, Tom Lane wrote:
> Because it's really an OID under the hood.
I see.
> > In any use cases, that I know,
> > ENUM 255 values (1 byte) more then enough.
> Only if you consider each enum type in isolation (and even then, I'd
> dispute your argument that nobody has use for more than 255 values).
May be, but then it must be filled not by programmer in code, but
instead by operator of DB or script or routine. In such case it must be
filled by DML, but not DDL statements, thus it must be a foreign table.
But for what ENUM must be for and when is it better, then foreign
table? When set of values is stable, small and can be predefined by
programmer himself. For instance, very common field for each database
is "sex". What type to use for this? Boolean, because it is 1 byte?
It's ugly, boolean is for boolean arithmetics, not for predefined
values. And will need foreign table and one more "join" to translate
true and false to "male" and "female". Or special function for this.
Who will have "true sex" and who will be "false"? :) This is looking
like sexism. And what to do if this example will be not "sex" (for
which true and false enough, if without a trans-gender or genetic
mosaicism), but other type that need 3 values? Char(1) and 'm'/'f'?
Looked good and can be used without one more "join", but will take 2
bytes for English, 3 bytes for European language, and even more for
Asian. Foreign table and smallint? 2 bytes and one more "join". Bit
string? Yep, can be only 1 byte, but using a bit string for this is
also very ugly. PostgreSQL don't have a suitable type for such common
problem as defining "sex" in DB or similar problem. But has ENUM that
is in the current implementation is not obvious when it can be better
then a old school foreign table.
> Other ways of doing it would have created problems of their own.
May be complex primary key: one field is oid of type of enum, other is
byte.
> But
> you can certainly build your own enum type if you don't like the
> tradeoffs
> the core code made.
Good idea. I'll think about this.