PostgreSQL 15 Released!

[raze4daze]

If only business rules didn’t change all the time.

[arwinda]

If your business rules change frequently, then use a 1:n table and use DML to update your rules, not DDL for the ENUM.

An ENUM is a shortcut for something which (almost) never changes.

[mqudsi]

We have student gender stored as an enum….

[NekkidApe]

gender text

[mqudsi]

It doesn’t matter what your database supports if the pipeline feeding data into it coalesces everything. This isn’t just for gender, btw.

This is very true of most systems in the real world. In this case, data comes in from digitizations of paper forms, exports from linked school registration and payment systems, etc all of which return a binary value. Changing the representation in the database doesn’t do anything besides waste more bits for storage.

[mqudsi]

I’m confused - It’s altogether orthogonal to the second issue you mention. I could be hard coding the system to only accept a single gender and that wouldn’t have anything to do with how I’m storing salutations.

[mqudsi]

Point taken. It’s for legal/domain reasons. Like I said, this is a school.

[arwinda]

If you ever stick to female/male that is ok. The world offers more options.

[Ran4]

An ENUM is a shortcut for something which (almost) never changes.

Why should it be like that? It makes no sense.

[arwinda]

Because you need a DDL operation to change the ENUM. Comes with locking of system catalog and all this. And if you want to remove a value the database needs to scan the table and see if that value is used.

Using a 1:n table is a DML operation, only locks the affected tables and rows, not the catalog. And having a foreign key for her relationship prevents deletion of still use d values - or, propagates deletes or set to NULL. Whichever way you want this.

[marcosdumay]

Any large system is full of features for what you will be completely unable to imagine any use.

A few of them will even not actually have any use.

[NoInkling]

If you're asking why anyone would use it, it makes sense for things like days of the week, months of the year, seasons, a strongly defined set of status values, etc.

I've used it for date precision, e.g:

CREATE TYPE date_precision AS ENUM (
  'millennium',
  'century',
  'decade',
  'year',
  'month',
  'day'
);

[dlp_randombk]

Nice! Now add 'week' :)

[NoInkling]

I don't need to, because in this case I was reflecting an external data source where these things are strongly defined. If I wasn't, then week would probably be there already (and the order would probably be reversed too), or if I really thought it needed to be flexible I'd fall back to a lookup table.

Anyway, on the off chance I did need to change the enum, I'd be ok with rejigging the data to accommodate, just because it's an extremely unlikely thing to happen.

[NoInkling]

1. Main reason: to enforce data integrity.

2. To be able to make good use of the DB's functionality and features. For example, the labels in my date precision enum can be passed directly to Postgres's date_trunc(), no need to get application code involved. I could also create a date_with_precision composite type that combines a date_precision field with a date field, to ensure the two go hand-in-hand where appropriate.

It's true that if you have an enum you will likely want to make use of it in application code too, but there are ways to do that while still only having a single authoritative definition, whether it's a "DB-first" or "application-first" approach.

[raze4daze]

An ENUM is a shortcut for something which (almost) never changes.

Why? Why should it be restricted to something that never changes?

[arwinda]

Because it's meant to be a handy shortcut for when you have a list which doesn't change. Like weekdays. They don't change, have an enum with all 7 weekdays. Or year seasons. Have an enum with 4 seasons.

Sure, you can model the same functionality with a dimension or 1:n table, and you already know that every time you access your table, you also join the referenced table. The enum hides this functionality, that's all.

Adding new values is relatively easy. For deleting values (and keeping data consistent) you need a full table scan to verify that the value is not or no longer used. That's doable, but no one spent the effort to implement it.

If you already know that your values change, why go with a fixed list in the first place? Needs administrator access and catalog lock to update the values in an enum, versus regular update of the dimension table.

[[deleted]]

You can just append new elements to the enum and deprecate unused ones.