Representing Enums in PostgreSQL

Glad to see this as the top comment, completely agree. After reading the article, was thinking that the only real downside of using enum types (but there is one more issue not mentioned, more on that below) is when you need to remove values. In reality, I've found removing enum values to be a very rare occurrence in prod. Removing an enum value fundamentally breaks backwards compatibility, so usually a better option is to essentially deprecate it, which is more like what you recommend.

Note one other thing I have found is that postgres enums are less "portable" than plain text columns. For example, if you're using BigQuery to query postgres tables as an external connection, BQ doesn't support enums, so your external connection query has to cast any enum return values (e.g. some_enum_column::text) before returning them to BQ, which can be a pain if you want to do "SELECT * FROM ..." some table that returns enum columns.

Good approach. And with Postgres `DOMAIN` types features to DRY up the `CHECK` the live constraint, this should be very palatable. I usually opt for enum tables and regularly find myself needing to declare `archived_at` columns for said tables. Did not know of the `RENAME` command either and what you describe renders native enums less warty.

Agree, PostgreSQL enum is really just for mapping with an enum in application code. And just like you should not remove an enum or change their ordinal in application code, don't do that to a PostgreSQL enum

I find it odd how many schema changes in modern RDBMS must be done on the whole table at once. You can split a table in chunks and recode each chunk gradually in a way which doesn't change the data in it (so no downtime) but removes dead entries like updated enums.

In a way you describe how we can emulate this process. The question is why the heck wouldn't databases do this themselves? Same with adding and dropping columns.

Consider how PostgreSQL encodes null for example, by skipping them in the row as fields, and adding them in a null bitmap in front of the row. Meaning... rows are not uniformly sized, there's no math like offset = row * rowsize + field_offset; kind of addressing for reading a field in PG where recoding some of the rows breaks the entire table.

And yet we have all those huge monolithic operations that need to be done atomically. So weird.

I prefer directly using strings as enums, and using the foreign key constraint only to validate enum values.

    CREATE TABLE my_enum ( name TEXT PRIMARY KEY );

    CREATE TABLE foo ( my_enum TEXT REFERENCES my_enum (name) );

Yep thats what I typically do except a string "code" and string "description" that way the raw data is more readable without joining the coded value and many times you want a short code vs long description, like with US states and their abbreviations. Most codes are 1 or 2 characters up to maybe 4 so you end up with less or the same space used than a 32 bit int with UTF-8.

Metadata like sorting and even what date ranges the code is valid for and even security as in who's allowed to use the code in the app.

This is... exactly what PG does under the covers for ENUM types. And also of course this is historically the canonical way to do ENUMs in SQL.

This is exactly what I do, save with natural keys.

In my experience, you need those descriptions and other metadata on these enum values more often than not.

PG enums are "enum tables" under the hood. With native enum support the JOINs with the enum tables happen at query compilation time, which is a performance benefit you should not ignore.

PostgreSQL enums are there for you to use on your code API. They can go into tables because sometimes it's important to put code data into tables.

What they are absolutely not for is to replace the one standard way to define data enums that everybody use since relational algebra was created. It's such an outrageously naive idea that I'm sure most people here were attracted to the title thinking it's about algebraic types or some other similar misunderstanding.

> Is there a reason ALTER TYPE name ADD VALUE new_enum_value wouldn't Just Work for the first example for 99% of use cases?

I'm sorry, we don't have any examples in the article about adding values to enums, so I don't know to what you are referring here.

> Yes, that's true, but also very rare?

It is rare, indeed, but we just have a strong inclination towards having a clean schema, so we do prefer to clean up things that would otherwise be unused. But I understand that many people would prefer to leave them hanging there, even if they are not going to be used in any way.

The "less space" argument does not seem to be true in every situation. Enums may suffer from alignments. See here: https://dba.stackexchange.com/a/258591/50410

Isn’t index size more proportional to number of distinct values and number of rows. With few distinct enum options, the size of each shouldn’t have big impact?

Whew, good to know! Another point towards the `CHECK` constraint approach. Thanks! :)

It sure sounds like a bug to me, too! Is there an upstream bug id? I’d imagine dump/restore should “just work” with anything that can be done in postgres oob.

> Adding entire tables just doesn't seem worth it for this use case alone.

If you peek at a particular table often enough and it has a crazy enough enum to maybe justify making your life a little easier, what you can do is add a table just for debug purposes and use a view that maps the int to it, but otherwise only use the direct int:enum cast in your app/orm/etc. thereby not paying any runtime cost for foreign keys or integrity checking.

> Adding entire tables just doesn't seem worth it for this use case alone.

I am going down this path. There are a lot of downstream use cases (e.g. support, reporting, etc) that can benefit from having the canonical names & human-friendly labels in the database.

Keeping database in sync with code like this requires some discipline and manual scripting, but I think it's worth it.

> I usually use C# Enums translated by EF Core now (...)

Does this approach ensure data consistency? Or could you accidentally insert a number value into your table which is not represented by any of the enum values?

IDK I use the npgsql enum mapping to native pg enum and it works great. Pretty sure I have it configured for string values too.

Foreign keys have a different set of tradeoffs. We'd have to either look enum values up before making the query, or use a join that might affect query plan.

That would be an improvement on the `CHECK` constraint approach if the same `CHECK` constraint is used in multiple columns, to DRY it up. The `DOMAIN` types offer the same commands for `CHECK` constraints, namely `NOT VALID` when updating the constraint, and `VALIDATE CONSTRAINT` to make sure it is valid after having updated it.

Apologies for my ignorance, but what does "FDW" stand for in your comment? :)

I had to implement a workaround for that for our DW which imports from two different app databases. IMO this should be part of

  import foreign schema

  DROP SCHEMA IF EXISTS fdw_enum CASCADE;
  CREATE SCHEMA fdw_enum;
  
  -- Get names of the enums using ::regtype casting and label and sort_order from app_a and app_b.
  CREATE VIEW fdw_enum.app_a_enums AS SELECT * FROM dblink('fdw_app_a',
  $QUERY$
    SELECT
      enumtypid::regtype AS name,
      enumsortorder sort_order,
      enumlabel label
    FROM pg_enum;
  $QUERY$
  ) AS t (name text, sort_order integer, label text);
  
  CREATE VIEW fdw_enum.app_b_enums AS SELECT * FROM dblink('fdw_app_b',
  $QUERY$
    SELECT
      enumtypid::regtype AS name,
      enumsortorder sort_order,
      enumlabel label
    FROM pg_enum;
  $QUERY$
  ) AS t (name text, sort_order integer, label text);
  
  -- Ensure enums with the same names aren't defined in both app_a and app_b.
  DO
  $DO$
  DECLARE
    matching_count integer;
  BEGIN
    SELECT COUNT(*) into matching_count
    FROM fdw_enum.app_a_enums INNER JOIN fdw_enum.app_b_enums USING (name);
    ASSERT
      matching_count = 0,
      'app_a and app_b-NG have identically named enums. Implement a check that they are identically defined.';
  END
  $DO$;
  
  CREATE VIEW fdw_enum.upstream_enums AS
  SELECT * FROM fdw_enum.app_a_enums
  UNION ALL
  SELECT * FROM fdw_enum.app_b_enums;
  
  
  CREATE PROCEDURE fdw_enum.create_type_if_not_exists(name text)
  LANGUAGE plpgsql
  AS $PROC$
    BEGIN
      EXECUTE format('CREATE TYPE %s AS ENUM ()', name);
      EXCEPTION WHEN duplicate_object THEN RAISE NOTICE 'Enum type % already exists, skipping', name;
    END;
  $PROC$
  ;
  
  
  -- To make this idempotent we create the enums empty then alter them by adding values.
  -- So instead of `CREATE TYPE foo AS ENUM ('bar', 'baz');` we do
  -- `CREATE TYPE IF NOT EXISTS foo AS ENUM ();
  -- `ALTER TYPE foo ADD VALUE IF NOT EXISTS ('bar');
  -- `ALTER TYPE foo ADD VALUE IF NOT EXISTS ('baz');
  -- This ensure the procedure is re-runnable and can add new values to the enum as they are added upstream.
  -- Order is ensured by the ORDER BY in the loop query.
  CREATE PROCEDURE fdw_enum.refresh_upsteram_enums()
  LANGUAGE plpgsql
  AS $PROC$
  DECLARE
    rec record;
    ddl text;
  BEGIN
    FOR rec IN SELECT DISTINCT(name) AS name FROM fdw_enum.upstream_enums
    LOOP
      CALL fdw_enum.create_type_if_not_exists(rec.name);
    END LOOP;
  
    FOR rec in
      SELECT *
      FROM fdw_enum.upstream_enums
      ORDER BY name, sort_order
    LOOP
      ddl := FORMAT('ALTER TYPE %s ADD VALUE IF NOT EXISTS %s', rec.name, quote_literal(rec.label));
      EXECUTE ddl;
    END LOOP;
  END
  $PROC$
  ;
  
  CALL fdw_enum.refresh_upsteram_enums();

An enum stores a small reference to the enum value in the tuples, and the constraint approach stores the string itself. The constraint approach takes more space with (a) larger enum values, (b) more columns using them, and (c) more rows in the tables that use them. The difference depends on the characteristics of the database we're talking about.

Just to make sure I understand your suggestion fully, you're saying: 1. Use `smallint` instead of `text` for the column type. 2. Otherwise follow our "`CHECK` constraint" approach (without it we're jeopardizing data consistency because we can store anything between -32768 and 32767 in that column). 3. Translate the int to the enum in your application.

Right?

I don't like this approach much because getting cryptic integers when you do a `SELECT` in the database is really cumbersome.