May 28, 2025
I'm working on cleaning some data, and I'm identifying the bad data by trying to enter it into tables with constraints and domains. I have not used domains until today. Postgres's glossary defines a domain as "A user-defined data type that is based on another underlying data type. It acts the same as the underlying type except for possibly restricting the set of allowed values" and then points you to section 8.18 for more information.
I've found three situations where domains are more useful than constraints:
- Since they are types, they can be defined once and used in multiple places. You cannot do this with constraints. If you want to use the same constraint on multiple columns, you have to write out the exact same text on each of them.
- When a value violates the constraint on a domain - at least using Postgres's COPY function - it tells you the specific value (and only that value) that violated it. If a constraint on a regular type is violated, the error is less specific and prints out the entire row (or part of it) where the violation occurred. If there are many columns in the row, it makes it difficult to identify which value caused the issue.
- Domains can be redefined. In my work, I first defined a domain with a constraint that was supposed to match the data. If there was a violation, I redefined the domain with a less restrictive constraint until there was no violation. My work in identifying issues is thus shown in the code. You cannot do this with constraints, at least not in the same way. Constraints don't replace each other; you'd have to erase or comment out a more restrictive one to get to a less restrictive one.