Joe Abbate <jma@xxxxxxxxxxxxxxxxx> writes: > I'm considering creating a TYPE for what may be called a "possibly > imprecise date" (pidate). > The first option I explored was creating a composite type with the two > attributes, but that doesn't allow specification of DEFAULTs, NOT NULL > or CHECK expressions on the precision code attribute. It seems I'd have > to create a DOMAIN first, then use DATE and that domain to create a > composite TYPE, to finally use the latter in actual tables. That > layering looks cumbersome. Agreed. > Another option, which I havent't tried, is to subvert PG by creating an > empty table, since that creates a "record type", but even if possible > that would be a hack. Won't help. Even if the table has constraints, when its rowtype is used in a standalone context, it only has the features that a standalone composite type would have (ie, no constraints). > Am I overlooking something or is the practice of creating abstractions > in object-relational databases mostly unchanged? 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. 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. regards, tom lane
