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

From: Daniel Popowich <dpopowich(at)artandlogic(dot)com>
To: pgsql-general(at)lists(dot)postgresql(dot)org
Subject: Operators on ranges with a domain subtype do not implicitly cast operands of the domain's base type
Date: 2022-06-16 19:59:37
Message-ID: CACjxne65uMMQoVApgdh6sV6UK9bnC6Vw3dxgCwi005tvQ81zaA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Tom Lane 2022-06-16 20:26:39 Re: Operators on ranges with a domain subtype do not implicitly cast operands of the domain's base type
Previous Message Tom Lane 2022-06-16 14:54:27 Re: ERROR: failed to find conversion function from key_vals_nn to record[]