Daniel Popowich <dpopowich@artandlogic.com> writes:
> -- domain with underlying type of integer (what constraints we might
> -- place on the integer values are not germane to the issue so they're
> -- left out).
> create domain zzzint integer;
> -- a range on our domain
> create type zzzrange as range (subtype = zzzint);
Why is this a good idea?
ISTM the subtype of a range type shouldn't really be a domain.
In my case I have an integer representing a tax year. Early in development I found this "type" cropping up all over my schema and application logic. Everywhere it occurred I was placing the same check constraints to make sure it was an integer in our expected range of values, I didn't want years prior to a certain year, or years beyond one year into the future. Didn't want people fat-fingering "2202", so:
CREATE DOMAIN taxyear INTEGER CONSTRAINT taxyear_range CHECK (value BETWEEN 1980 AND date_part('year', CURRENT_DATE) + 1);
This provides useful semantics throughout my schema and application code (taxyear vs integer) and good data validation. Really cleans up the code. If the lower end of the range changes, I only have to change it in one place, etc.
Meanwhile, there are entities in my data modeling that accept ranges of tax years. A questionnaire, for example, that might apply to a contiguous range of years. Or a "study" of tax years, say, from 2018-2021. I could have implemented such models with begin/end years, but why? The years are always contiguous and I have the benefit of range operators, eg. given a range I can now use `some_range @> some_taxyear` in a filter. Very powerful, clean, expressive. Thus I created:
CREATE TYPE tyrange AS RANGE (subtype = taxyear);
And so, here I am, getting user input of "2017" and expressions like
SELECT * FROM questionnaire WHERE years
@> 2017;
Are blowing up with:
