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