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@sss.pgh.pa.us> wrote:

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:

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