Re: Operators on ranges with a domain subtype do not implicitly cast operands of the domain's base type

On Thu, Jun 16, 2022 at 4:26 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Daniel Popowich <dpopowich(at)artandlogic(dot)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:

ERROR: operator does not exist: tyrange @> integer
LINE 1: select * from questionnaire where years @> 2017;

^
HINT: No operator matches the given name and argument types. You might
need to add explicit type casts.

Forcing me to do explicit casts everywhere. Given the underlying type of
taxyear is INTEGER and the operand is an INTEGER I'm finding this puzzling
why this is so difficult.

Hope that explains.
Daniel