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

Поиск
Список
Период
Сортировка
От Daniel Popowich
Тема Operators on ranges with a domain subtype do not implicitly cast operands of the domain's base type
Дата
Msg-id CACjxne65uMMQoVApgdh6sV6UK9bnC6Vw3dxgCwi005tvQ81zaA@mail.gmail.com
обсуждение исходный текст
Ответы Re: Operators on ranges with a domain subtype do not implicitly cast operands of the domain's base type  (Tom Lane <tgl@sss.pgh.pa.us>)
Re: Operators on ranges with a domain subtype do not implicitly cast operands of the domain's base type  (Bryn Llewellyn <bryn@yugabyte.com>)
Список 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
    ----------------------------------------------------------------------

В списке pgsql-general по дате отправления:

Предыдущее
От: Tom Lane
Дата:
Сообщение: Re: ERROR: failed to find conversion function from key_vals_nn to record[]
Следующее
От: Tom Lane
Дата:
Сообщение: Re: Operators on ranges with a domain subtype do not implicitly cast operands of the domain's base type