Technical Evaluations

PostgreSQL: ENUM is no Silver Bullet

4 ways to do inclusion validations in PostgreSQL

Photo by Lorenzo Herrera on Unsplash

It is a common situation where the value of a field is restricted to a list of valid options.

For examples,

• Genders: male, female , …
• Currencies: USD, SGD, CAD, AUD, …
• Countries: us, uk , sg… (200+ options)
• Statuses: pending , processing , failed , completed , …
• Genres: romance , comedy , action , …

Data validation and integrity

Although data validation can be done at the APPLICATION LOGIC LAYER either

• on the client-side (form validations)
• OR on the server-side (API or model validations),

such validations can be bypassed by going straight to the lower layer.

The safer way to ensure data integrity is by using DATABASE LEVEL CONSTRAINTS or VALIDATIONS.

This article would be discussing 4 techniques in which we can restrict the values to a set of valid options in PostgreSQL (also applicable to other databases)

1. Enumerated Types
2. Simple CHECK…