Operators on ranges with a domain subtype do not implicitly cast operands of the domain's base type
I'm using PG 14 and have an application using a custom range with a custom
domain subtype. My problem: PG does not do an implicit cast from the
domain's base type when used with range operators. Below is a script that
will demonstrate the problem (and below that, the output of running it with
psql).
What I'm looking for: the magic to add to my schema so I do not have to add
explicit casts throughout my application code when using the base type of a
domain as an operand to a range operator using a subtype of the domain. How
do we get implicit casts?
Thanks!
Daniel
Here's my script. Note it creates a schema to isolate what it generates.
Output of running it follows.
----------------------------------------------------------------------
\set ECHO all
\set VERBOSITY verbose
select version();
create schema _range_domain_cast;
set search_path to _range_domain_cast,public;
-- 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);
-- similar range, but on an integer
create type myintrange as range (subtype = integer);
-- these work
select myintrange(10, 20) @> 15; -- subtype is integer and this
just works
select zzzrange(10, 20) @> 15::zzzint; -- subtype is zzzint and this
works with the explicit cast
-- as does using integer where zzzint is expected
create table foo (
x zzzint
);
insert into foo select * from generate_series(1,3);
select * from foo;
-- But this fails! - without the explicit cast, PG doesn't do the
implicit cast
-- even though integer is the underlying type of the domain
select zzzrange(10, 20) @> 15;
----------------------------------------------------------------------
Here is the output when running it:
\set VERBOSITY verbose
select version();
version
--------------------------------------------------------------------------------------------------------------
PostgreSQL 14.2 on x86_64-pc-linux-musl, compiled by gcc (Alpine
10.3.1_git20211027) 10.3.1 20211027, 64-bit
(1 row)
create schema _range_domain_cast;
CREATE SCHEMA
set search_path to _range_domain_cast,public;
SET
-- 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;
CREATE DOMAIN
-- a range on our domain
create type zzzrange as range (subtype = zzzint);
CREATE TYPE
-- similar range, but on an integer
create type myintrange as range (subtype = integer);
CREATE TYPE
-- these work
select myintrange(10, 20) @> 15; -- subtype is integer and this
just works
?column?
----------
t
(1 row)
select zzzrange(10, 20) @> 15::zzzint; -- subtype is zzzint and this
works with the explicit cast
?column?
----------
t
(1 row)
-- as does using integer where zzzint is expected
create table foo (
x zzzint
);
CREATE TABLE
insert into foo select * from generate_series(1,3);
INSERT 0 3
select * from foo;
x
---
1
2
3
(3 rows)
-- But this fails! - without the explicit cast, PG doesn't do the
implicit cast
-- even though integer is the underlying type of the domain
select zzzrange(10, 20) @> 15;
ERROR: 42883: operator does not exist: zzzrange @> integer
LINE 1: select zzzrange(10, 20) @> 15;
^
HINT: No operator matches the given name and argument types. You might need
to add explicit type casts.
LOCATION: op_error, parse_oper.c:647
----------------------------------------------------------------------
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.
The range type depends very fundamentally on the ordering properties
of the subtype, so trying to put some abstraction in there seems a
bit misguided. Moreover, there are a whole bunch of weird semantics
issues that arise if the domain tries to restrict the set of allowed
values. For instance, if the domain disallows "3" (maybe it allows
only even integers) then what does a range (2,10) really mean?
Should we be expected to figure out that it's effectively [4,10)?
What pitfalls does that create for, say, multirange operators?
You could usefully make a domain over the range type and put some
restrictions at that level, perhaps.
regards, tom lane
dpopowich@artandlogic.com <mailto:dpopowich@artandlogic.com> wrote:
I'm using PG 14 and have an application using a custom range with a custom domain subtype. My problem: PG does not do an implicit cast from the domain's base type when used with range operators.
I hit what looks to be the same issue. I reported in here:
/messages/by-id/37D76918-6FD7-4598-A409-A7091687D8E6@yugabyte.com </messages/by-id/37D76918-6FD7-4598-A409-A7091687D8E6@yugabyte.com>
David Johnston said that he thought that it was a bug.
My workaround is to typecast the two operands explicitly back to their base types.
I don't think that there's any magic to fix it declaratively. My guess is that you might work around it with a user-defined operator for the domains in question that hides the typecasts in its implementation function. (This has worked for me in other cases for other reasons. But I didn't try that in my testcase.)
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 sothey'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
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 sothey'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
Show quoted text
Hope that explains.
Daniel