Joe Abbate <jma@freedomcircle.com> 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