Re: Improve upcasting for INT range and multi range types

[Federico]

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

Improve upcasting for INT range and multi range types

[Federico]

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

season of docs proposal

[Federico Razzoli]

I am interesting in working for a PostgreSQL project, If it's not already
taken, my preference is "Write a PostgreSQL technical mumbo-jumbo
dictionary ".

My name is Federico Razzoli. Some facts about me:
* I am a database consultant.
* In the past as a DBA I administered PostgreSQL for several companies
(mostly for analytics, to be honest)
* I have some experience in writing. I authored "Mastering MariaDB" and
"MariaDB Essentials". My blog (federico-razzoli.com) has several technical
articles. I occasionally speak at conferences, like Percona Live.
* Located in London.

I regularly contribute to some other communities, but I've never been
involved in the PostgreSQL community. I would be happy to start with this
project.

Cheers,
Federico