sql: support ENUM types

[jseldess]

Special case of #25123.

Feature request: Postgres supports ENUM types for defining columns that can contain only a given set of values. CockroachDB should, too.

https://www.postgresql.org/docs/10/static/datatype-enum.html

Special note:

Whenever a user-defined type is created (with CREATE TYPE), PostgreSQL automatically creates an associated array type, whose name consists of the element type's name prepended with an underscore, and truncated if necessary to keep it less than NAMEDATALEN bytes long. (If the name so generated collides with an existing type name, the process is repeated until a non-colliding name is found.) This implicitly-created array type is variable length and uses the built-in input and output functions array_in and array_out. The array type tracks any changes in its element type's owner or schema, and is dropped if the element type is.

[danhhz]

This would also be an important building block in query planning for some instances of partitioned tables. Some background on this at https://github.com/cockroachdb/cockroach/blob/2f2cb3af3df0bebf01bd2f0bb9c8329ad44229df/docs/RFCS/20170921_sql_partitioning.md#query-planning-changes

[knz]

cc @awoods187 for prioritization

[awoods187]

Split SET into a new issue #27415

[ghost]

In which release can we expect this feature?

[knz]

It's not planned yet.

[orefalo]

No Enum? that's a show stopper for me.

don't understand what makes this simple constraint so difficult to implement.

[knz]

Hello Olivier! Thank you for your feedback! We love external contributions! We also would love to guide you to implement this simple constraint! -- Sent from my Android device with K-9 Mail. Please excuse my brevity.

[orefalo]

Thank you. But then.. custom types are also missing, and stored procedure also seem to have their issues.
Hum.. It's not going to be that easy to get Graphile working against cockroach.

;-)

[allenvino1]

Very excited for this 👍

[Y0ngg4n]

Any updates on this Feature?

[jordanlewis]

@Y0ngg4n, we're likely to work on it for our 20.2 release, which will be in late 2020. Unfortunately we didn't get to it for 20.1, which is going to be released in the next month or so.

[rohany]

Hi all -- we're looking into implementing this and are wondering how often the postgres supported behavior of

alter type t add value v [BEFORE | AFTER] v2

is used. This syntax adds a new value to an existing enum but affects how it is ordered against other elements in the enum, rather than just ordering it larger than all existing elements.

Thanks!

[hazcod]

@rohany I never used ordering within an enum to be honest.

[lukaseder]

@rohany A customer of ours is using this feature in newer versions of PostgreSQL. They wrote their own database change management utility that can logically reorder columns in tables as well to make sure every schema on every installation and environment always looks the same.

The benefit is when development uses feature branching, and two branches add columns (or enumeration values) in different order, depending on how they're merged and linearised, there are no unwanted and untested side effects due to ordering. I see this as a nice feature from a QA perspective.

Yes, it's possible to code all logic to be resilient to ordering changes, but that's a lot of effort, which can be avoided.

[karlseguin]

Internally, will they be stored as integers, thus yielding a performance win? Or is the initially goal just to enforce the constraint?

[rohany]

They won't be integers internally, but we have a separate representation for them that will use only a single byte in most cases! You can read about it here https://github.com/cockroachdb/cockroach/blob/master/docs/RFCS/20200331_enums.md#physical-layout.

[rohany]

Marking this as done!