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

From Pavel Stehule
Subject Re: Operators on ranges with a domain subtype do not implicitly cast operands of the domain's base type
Date
Msg-id CAFj8pRC24ZUiqk2waVwS9fdFCRGg2juZ1DpwTwTTzTg9Om+-8A@mail.gmail.com
Whole thread Raw
In response to Re: Operators on ranges with a domain subtype do not implicitly cast operands of the domain's base type  (Daniel Popowich <dpopowich@artandlogic.com>)
List pgsql-general


pá 17. 6. 2022 v 17:26 odesílatel Daniel Popowich <dpopowich@artandlogic.com> napsal:
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.

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

pgsql-general by date:

Previous
From: Daniel Popowich
Date:
Subject: Re: Operators on ranges with a domain subtype do not implicitly cast operands of the domain's base type
Next
From: Bryn Llewellyn
Date:
Subject: Re: '{"x": 42, "y": null}'::jsonb != '{"x": 42}'::jsonb ... Really?