Enum

Alright, let’s settle a classic architectural debate that has ruined more coffee breaks than a faulty espresso machine: should you use an ENUM type in your database, or should you use a good old-fashioned lookup table? On the surface, the choice seems trivial. But this is one of those foundational decisions that ripples through your application code, your data integrity, and your sanity for years to come. I’ve been on both sides of this fight, and I’m here to give you the unvarnished truth, not the textbook answer.

Alright, let’s get our hands dirty with enums. You’ve defined a beautiful, type-safe enum to bring order to the chaos of your domain. That’s step one. Now, step two is actually using those values in the real world, which means you need to know how to ask questions about them and put them head-to-head. It’s simpler than you think, but there are a few landmines the language designers left lying around, just to keep you on your toes.

Right, so you’ve built your beautiful, pristine enum type. It’s perfect. It’s elegant. It describes your domain with the precision of a poet. And then, reality happens. A new business requirement lands on your desk, and suddenly you need a new value. Don’t panic. This isn’t a crisis; it’s a Tuesday. PostgreSQL gives you the ALTER TYPE ... ADD VALUE command for exactly this reason, and it’s a lot more powerful—and occasionally infuriating—than it looks at first glance.

Right, so you’ve outgrown booleans. TRUE and FALSE are great until you need MAYBE, SOON, or WHY_IS_THE_DATABASE_LIKE_THIS. This is where enumerated types, or ENUMs, come in. Think of them as a way to create your own, custom-ordered set of labels that are far more meaningful and constrained than just dumping text into a VARCHAR field. They’re perfect for things like statuses (('pending', 'processing', 'completed', 'failed')), user roles, or any finite list of states where you want the database itself to enforce what’s allowed.

Let’s talk about the delightful mess that is getting a simple “yes” or “no” from a user. You’d think it would be straightforward, but humans are gloriously inconsistent creatures. We have to account for “YES”, “yes”, “Yes”, “Y”, “y”, “1”, “true”, “TRUE”, “t”, and, my personal favorite, the confidently incorrect “affirmative”. And that’s just for one of the two possible boolean states. The core problem is that we, the programmers, need a pristine True or False in our code, but we’re getting this data from the messy, unpredictable outside world: config files, user input in a CLI, forms on a website, or data serialized from another system. Your job is to build a robust sanitation layer that translates this human-friendly chaos into machine-friendly boolean values without driving yourself insane.

Alright, let’s talk about the boolean type. It seems simple, right? true or false. On/off. Yes/no. What could possibly go wrong? Well, you’d be surprised. This is SQL, after all, and it has a special talent for taking simple concepts and adding a dash of existential dread in the form of a third value: NULL. Let’s get the basics out of the way. You declare a boolean column just like any other type. You can insert the literal keywords TRUE and FALSE. But here’s the first “clever” thing PostgreSQL does: it has a whole list of “truthy” and “falsy” inputs it will gladly accept, because why not? It’s the friendly, accommodating type.