Hello Tom, On 28/9/20 17:25, Tom Lane wrote:
Domain-over-composite might be a slightly simpler answer than your first one. It's only available in relatively late-model PG, and I'm not sure about its performance relative to your other design, but it is an alternative to think about.
"Domain-over-composite" meaning create a TYPE first (DATE, CHAR(1)) and then a DOMAIN based on that type? (1) How late model are we talking? The DOMAIN syntax doesn't seem changed from PG 11 to PG 13? (2) Can a CHECK constraint specify attributes of the composite?
Note that attaching NOT NULL constraints at the domain level is almost never a good idea, because then you find yourself with a semantically impossible situation when, say, a column of that type is on the nullable side of an outer join. We allow such constraints, but they will be nominally violated in cases like that.
NULLs: Tony Hoare's "billion dollars of pain and damage" transported to SQL. Joe
