1

I have a simple table with a status column:

create type status as enum (
  'approved',
  'pending_approval',
  'rejected'
);

create table document (
  id          serial primary key,
  uuid        uuid unique not null default uuid_generate_v4(),
  title       text not null,
  content     text,
  status      status not null,
  created_at  timestamptz not null default now(),
  updated_at  timestamptz not null default now(),
  deleted_at  timestamptz
);

This is the beginning of a small workflow-esque application I'm doing. Statuses, however, can be defined by the users of the application. What are the cons of using ALTER TYPE instead of creating a new table and using JOIN? I really don't want to add a JOIN to my queries just because of a status field.

3

What are the cons of using ALTER TYPE instead of creating a new table and using JOIN? I really don't want to add a JOIN to my queries just because of a status field.

This is your typical pro/con enum vs normalization question.

ENUM type

  • Great if the range is fixed or only requires modification by the DBA.
  • Get a really nice query syntax which optimized well (bottom assumes status is an ENUM)

    SELECT * FROM foo WHERE status = 'INVALID';
    
  • Sorts well and permits easy range within the type. All enums are sorted and have ordinality

  • Store in an 4 byte-size
  • Can eliminate a JOIN
  • PostgreSQL has ENUM types, not macros (like MySQL). This means you don't take a hit in maintenance. If you have five tables using your ENUM type you can still manage just that one type and not five tables.

Normalized / JOIN method

  • Always preferred if the user needs to be able to modify the domain/data
  • Always preferred if the domain/data needs to shrink (remove values)
  • Will provide easier modification with an ORM
  • More traditional
  • Can be smaller than ENUM (for instance smallint is two bytes, enum is always four bytes).
  • But why is it always preferred if the user needs to be able to modify the data? Why can't the application run the ALTER TYPE command? – vinnylinux May 1 '18 at 23:36
  • 1
    @vinnylinux: If the app has the necessary privileges, it can run ALTER TYPE. But that would be risky business. ALTER TYPE ... ADD VALUE cannot run inside a transaction block. So it cannot be rolled back. And it cannot be wrapped in a server-side function. So its power is not easily contained. And you cannot remove a value from an enum type without tricks. Etc. In short: don't go there. – Erwin Brandstetter May 2 '18 at 1:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.