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

pá 17. 6. 2022 v 17:26 odesílatel Daniel Popowich <dpopowich(at)artandlogic(dot)com>
napsal:

> 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.
>

This feature has not yet been implemented, maybe. Or forgotten. The type
system (and internal implementation) is pretty complex because an
overloading, polymorphics types, domains are supported.

Probably the fix will not be too difficult - but can be hard to rethink all
consequences and dependencies.

Regards

Pavel

>
> Hope that explains.
> Daniel
>