Re: Improve upcasting for INT range and multi range types
Hi, Thanks for the reply. I suspected that there were technical reasons that prevented the obvious right thing to be done. Would adding overloads to the functions and operators be something that could be considered as an acceptable solution? I've tried a very naive solution and it seems to work (there are for sure better options to declare the function overloads): begin; create function elem_contained_by_range(int4, int8range) returns boolean as $$ select elem_contained_by_range($1::int8, $2) $$ LANGUAGE SQL; create function elem_contained_by_range(int8, int4range) returns boolean as $$ select elem_contained_by_range($1, $2::text::int8range) $$ LANGUAGE SQL; create operator <@( LEFTARG = int4, RIGHTARG = int8range, FUNCTION = elem_contained_by_range, RESTRICT = rangesel, JOIN = contjoinsel, HASHES, MERGES ); create operator <@( LEFTARG = int8, RIGHTARG = int4range, FUNCTION = elem_contained_by_range, RESTRICT = rangesel, JOIN = contjoinsel, HASHES, MERGES ); select 2::int4 <@ '[1,9)'::int8range; select 2::int8 <@ '[1,9)'::int4range; rollback; The major drawback is that every combination operator - type would need its own overload creating a large number of them. As a side note it seems that int4range cannot be casted automatically to int8range. Best regards, Federico On Wed, 13 Dec 2023 at 05:16, Tom Lane wrote: > > jian he writes: > > Based on my interpretation, I don't think SELECT 2::INT4 <@ '[1, > > 4)'::INT8RANGE is doable. > > Yeah, it would require a considerable expansion of the scope of > what can be matched by a polymorphic operator. I'm afraid that > the negative consequences (mainly, "ambiguous operator" failures > because more than one thing can be matched) would outweigh the > benefits. It is kind of annoying though that the system can't > do the "obvious" right thing here. > > regards, tom lane
Re: Improve upcasting for INT range and multi range types
jian he writes: > Based on my interpretation, I don't think SELECT 2::INT4 <@ '[1, > 4)'::INT8RANGE is doable. Yeah, it would require a considerable expansion of the scope of what can be matched by a polymorphic operator. I'm afraid that the negative consequences (mainly, "ambiguous operator" failures because more than one thing can be matched) would outweigh the benefits. It is kind of annoying though that the system can't do the "obvious" right thing here. regards, tom lane
Re: Improve upcasting for INT range and multi range types
On Fri, Dec 8, 2023 at 4:21 AM Federico wrote: > > Hi, > > Postgresql seems to be missing upcasting when doing INT range and > multi-range operation, for example when checking if an int4 is inside > an int8 range. > Some non working example are the following > > SELECT 2::INT4 <@ '[1, 4)'::INT8RANGE > -- ERROR: operator does not exist: integer <@ int8range select oprname, oprleft::regtype, oprright::regtype, oprcode frompg_operator where oprname = '<@'; look at the results, you can see related info is: oprname | oprleft | oprright| oprcode -++---+-- <@ | anyelement | anyrange | elem_contained_by_range <@ | anyelement | anymultirange | elem_contained_by_multirange SELECT 2::INT4 <@ '[1, 4)'::INT8RANGE It actually first does an operator sanity check, transforms anyelement, anyrange to the detailed non-polymorphic data type. then calls the function elem_contained_by_range. but it failed at the first step. per doc https://www.postgresql.org/docs/current/extend-type-system.html#EXTEND-TYPES-POLYMORPHIC Similarly, if there are positions declared anyrange and others declared anyelement or anyarray, the actual range type in the anyrange positions must be a range whose subtype is the same type appearing in the anyelement positions and the same as the element type of the anyarray positions. If there are positions declared anymultirange, their actual multirange type must contain ranges matching parameters declared anyrange and base elements matching parameters declared anyelement and anyarray. Based on my interpretation, I don't think SELECT 2::INT4 <@ '[1, 4)'::INT8RANGE is doable.
Improve upcasting for INT range and multi range types
Hi,
Postgresql seems to be missing upcasting when doing INT range and
multi-range operation, for example when checking if an int4 is inside
an int8 range.
Some non working example are the following
SELECT 2::INT4 <@ '[1, 4)'::INT8RANGE
-- ERROR: operator does not exist: integer <@ int8range
SELECT 1::INT4 <@ '{[1, 4),[6,19)}'::INT8MULTIRANGE
-- ERROR: operator does not exist: integer <@ int8multirange
SELECT 1::INT2 <@ '{[1, 4),[6,19)}'::INT4MULTIRANGE
-- ERROR: operator does not exist: smallint <@ int4multirange
SELECT '[2, 3]'::INT4RANGE <@ '[1, 42)'::INT8RANGE
-- ERROR: operator does not exist: int4range <@ int8range
SELECT 2::INT8 <@ '[1, 4)'::INT4RANGE
-- ERROR: operator does not exist: bigint <@ int4range
etc.
In all these cases the smaller integer type can be upcasted to the
larger integer type.
Posted here since it doesn't seem like a bug, just a missing feature.
Thanks for reading
Federico
