> On Sep 28, 2020, at 3:14 PM, Joe Abbate <jma@xxxxxxxxxxxxxxxxx> 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.
