> On Sep 28, 2020, at 3:14 PM, Joe Abbate <j...@freedomcircle.com> wrote:
>
> Hello,
>
> I'm considering creating a TYPE for what may be called a "possibly imprecise
> date" (pidate). The most obvious use is for recording dates such as births
> or deaths of historical individuals, where we may know that someone died
> precisely on a given year-month-day, but the birth may only be known down to
> year-month or just the year (or perhaps we know precisely the baptism date
> [Adam Smith], but not the actual birth, so we want to record the former but
> qualified so it can be annotated on display). Another use is for
> publications, like magazines that are issued on a monthly basis or journals
> that are issued on a quarterly or seasonal basis.
>
> We currently have two instances of this kind, using a standard DATE column
> plus a CHAR(1) column that encodes (on a limited basis for now) the YMD, YM
> or Y level of precision, and a simple SQL function to return a textual
> representation of the pidate. It would be nice to generalize this before
> going further.
>
> 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.
>
> 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.
>
> Finally there's the base TYPE. This entails writing some seven functions "in
> C or another low-level language" (does PG support *any* other such
> language?), plus installing a library with those functions in a production
> environment. Doable, yes, but not very friendly either.
>
> Am I overlooking something or is the practice of creating abstractions in
> object-relational databases mostly unchanged?
>
> Regards,
>
> Joe
>
>
just record all three fields (day, month, year) with nulls and do the to-date
as needed.
