Is it possible to create an ENUM of smallints in postgres 11.x?

I’d like to create an enum TYPE to constrain a pl/pgsql function to accept an argument that’s 1, 2, or 3. Enum types seemed like the way to go about this.

I’ve tried creating my enum as below:

CREATE TYPE my_enum AS ENUM ('1'::smallint, '2'::smallint);
/*                           ^ syntax error here         */

Answer

According to the PostgreSQL documentation, an ENUM type is always going to be a fixed value:

An enum value occupies four bytes on disk. The length of an enum value’s textual label is limited by the NAMEDATALEN setting compiled into PostgreSQL; in standard builds this means at most 63 bytes.

Therefore, you can’t cast it into a smallint — sorry!

Instead, I suppose you can create a reference table and create a foreign key constraint, if space is your concern.

Disclosure: I work for EnterpriseDB (EDB)