Thread: Operator is not unique

If I do ...
select 1::NUMERIC + 1::BIGINT;

I get ...
[42725] ERROR: operator is not unique: numeric + bigint Hint: Could not
choose a best candidate operator. You might need to add explicit type casts.

This error means I have more than one way to calculate that formula ?
Did I create that operator erroneously ?
If so, how can I see what I did and how to fix it ?

I tried but didn´t find any answer.
select * from pg_operator
join LATERAL ( select typname::text from pg_type where pg_type.oid = oprleft
) l(oprleft_name) on TRUE
join LATERAL ( select typname::text from pg_type where pg_type.oid =
oprright ) r(oprright_name) on TRUE
join LATERAL ( select nspname from pg_namespace where pg_namespace.oid =
oprnamespace ) n(namespace) on TRUE



--
Sent from: https://www.postgresql-archive.org/PostgreSQL-general-f1843780.html

I don´t know if I did.

PostgreSQL 11.5 (Ubuntu 11.5-1.pgdg16.04+1) on x86_64-pc-linux-gnu, compiled
by gcc (Ubuntu 5.4.0-6ubuntu1~16.04.11) 5.4.0 20160609, 64-bit



--
Sent from: https://www.postgresql-archive.org/PostgreSQL-general-f1843780.html

PegoraroF10 <marcos@f10.com.br> writes:
> If I do ...
> select 1::NUMERIC + 1::BIGINT;
> I get ...
> [42725] ERROR: operator is not unique: numeric + bigint Hint: Could not
> choose a best candidate operator. You might need to add explicit type casts.

This doesn't happen for me.

> This error means I have more than one way to calculate that formula ? 
> Did I create that operator erroneously ?

A more likely theory is that you made some ill-advised changes to casting
rules.  If, say, the bigint to numeric and numeric to bigint casts are
both marked implicit, the parser won't have any way to decide whether it
should use "numeric + numeric" or "bigint + bigint" here.

            regards, tom lane

Nope, seems I didn´t create anything.

SELECT * FROM pg_operator WHERE oid = '+(numeric, bigint)'::regoperator;
ERROR: operator does not exist: +(numeric, bigint)

Ok, I can cast, it works. But why works without casting for you and not for
me ?



--
Sent from: https://www.postgresql-archive.org/PostgreSQL-general-f1843780.html

On 9/24/19 7:18 AM, PegoraroF10 wrote:
> Nope, seems I didn´t create anything.
> 
> SELECT * FROM pg_operator WHERE oid = '+(numeric, bigint)'::regoperator;
> ERROR: operator does not exist: +(numeric, bigint)
> 
> Ok, I can cast, it works. But why works without casting for you and not for
> me ?

Per Tom's suggestion try:

select format_type(castsource, NULL), format_type(casttarget, NULL), 
castfunc, castcontext, castmethod  from pg_cast  where castsource = 
'numeric'::regtype or castsource = 'bigint'::regtype order by castsource;

> 
> 
> 
> --
> Sent from: https://www.postgresql-archive.org/PostgreSQL-general-f1843780.html
> 
> 
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com

On 9/25/19 6:38 AM, PegoraroF10 wrote:
> Doing a inner join with pg_proc I´ll get this result. So, none of this 
> casts I´ve created. Column prorettype is different, this is the problem 
> ? select format_type(castsource, NULL), format_type(casttarget, NULL), 
> castfunc, castcontext, castmethod, pr.* from pg_cast inner join pg_proc 
> pr on castfunc = pr.oid where (castsource = 'numeric'::regtype or 
> castsource = 'bigint'::regtype) and format_type(casttarget, NULL) in 
> ('numeric','bigint') order by castsource;

Please run the query provided:

select format_type(castsource, NULL), format_type(casttarget, NULL), 
castfunc, castcontext, castmethod  from pg_cast  where castsource = 
'numeric'::regtype or castsource = 'bigint'::regtype order by castsource;


> format_type    format_type    castfunc    castcontext    castmethod    proname 
> pronamespace    proowner    prolang    procost    prorows    provariadic    protransform 
> prokind    prosecdef    proleakproof    proisstrict    proretset    provolatile 
> proparallel    pronargs    pronargdefaults    prorettype    proargtypes 
> proallargtypes    proargmodes    proargnames    proargdefaults    protrftypes 
> prosrc    probin    proconfig    proacl
> bigint    numeric    1781    i    f    numeric    11    10    12    1    0    0    -    f    false    false    true 
> false    i    s    1    0    1700    20    NULL    NULL    NULL    NULL    NULL    int8_numeric    NULL    NULL
NULL
> numeric    bigint    1779    a    f    int8    11    10    12    1    0    0    -    f    false    false 
> true    false    i    s    1    0    20    1700    NULL    NULL    NULL    NULL    NULL    numeric_int8    NULL 
> NULL    NULL
> numeric    numeric    1703    i    f    numeric    11    10    12    1    0    0    numeric_transform    f 
> false    false    true    false    i    s    2    0    1700    1700 23    NULL    NULL    NULL    NULL    NULL 
> numeric    NULL    NULL    NULL
> 
> 
> ------------------------------------------------------------------------
> Sent from the PostgreSQL - general mailing list archive 
> <https://www.postgresql-archive.org/PostgreSQL-general-f1843780.html> at 
> Nabble.com.


-- 
Adrian Klaver
adrian.klaver@aklaver.com

On 9/25/19 1:14 PM, PegoraroF10 wrote:
> format_type     format_type     castfunc     castcontext     castmethod
> bigint     smallint     714     a     f
> bigint     integer     480     a     f
> bigint     real     652     i     f
> bigint     double precision     482     i     f
> bigint     numeric     1781     i     f
> bigint     regclass     1287     i     f
> bigint     regtype     1287     i     f
> bigint     regconfig     1287     i     f
> bigint     regdictionary     1287     i     f
> bigint     regrole     1287     i     f
> bigint     regnamespace     1287     i     f
> bigint     bit     2075     e     f
> bigint     money     3812     a     f
> bigint     oid     1287     i     f
> bigint     regproc     1287     i     f
> bigint     regprocedure     1287     i     f
> bigint     regoper     1287     i     f
> bigint     regoperator     1287     i     f
> numeric     bigint     1779     a     f
> numeric     smallint     1783     a     f
> numeric     integer     1744     a     f
> numeric     real     1745     i     f
> numeric     double precision     1746     i     f
> numeric     money     3824     a     f
> numeric     numeric     1703     i     f
> 

Hmm, nothing strange here AFAICT.

What does:

select oprname, oprkind, oprleft, oprright, oprresult, oprcode  from 
pg_operator where oprname = '+';

show?


Have you made any changes/additions to CASTs and/or OPERATORs recently?

> 
> ------------------------------------------------------------------------
> Sent from the PostgreSQL - general mailing list archive 
> <https://www.postgresql-archive.org/PostgreSQL-general-f1843780.html> at 
> Nabble.com.


-- 
Adrian Klaver
adrian.klaver@aklaver.com

PegoraroF10 <marcos@f10.com.br> writes:
>       oprname      oprkind      oprleft      oprright      oprresult      oprcode
>       +      b      1700      2283      2283      pg_catalog.day_inc
>       +      b      2283      1700      2283      pg_catalog.day_inc

Hm, I wonder what that is.  It'd evidently match to numeric + anything.

            regards, tom lane

On 9/27/19 2:44 PM, PegoraroF10 wrote:
> oprname     oprkind     oprleft     oprright     oprresult     oprcode

> +     b     1700     2283     2283     pg_catalog.day_inc
> +     b     2283     1700     2283     pg_catalog.day_inc

In addition to Tom's comment about above, have you installed any 
extensions lately?


-- 
Adrian Klaver
adrian.klaver@aklaver.com

On 9/27/19 2:44 PM, PegoraroF10 wrote:
> oprname     oprkind     oprleft     oprright     oprresult     oprcode

> +     b     1700     2283     2283     pg_catalog.day_inc
> +     b     2283     1700     2283     pg_catalog.day_inc

Not sure if it will provide any useful information but try running:

select oprcode, pg_get_functiondef(oprcode)  from pg_operator where 
oprname = '+' and oprcode = 'pg_catalog.day_inc'::regproc;

-- 
Adrian Klaver
adrian.klaver@aklaver.com

SELECT * FROM pg_extension;
extname, extversion
plpgsql,1.0
pg_stat_statements,1.6
pg_trgm,1.4
tablefunc,1.0
unaccent,1.1
pageinspect,1.7




--
Sent from: https://www.postgresql-archive.org/PostgreSQL-general-f1843780.html

This select gives me:

ERROR: more than one function named "pg_catalog.day_inc"



--
Sent from: https://www.postgresql-archive.org/PostgreSQL-general-f1843780.html

On 9/30/19 5:48 AM, PegoraroF10 wrote:
> This select gives me:
> 
> ERROR: more than one function named "pg_catalog.day_inc"

In psql:

\df pg_catalog.day_inc

Or if you cannot get to psql then the query behind the above:

SELECT n.nspname as "Schema",
   p.proname as "Name",
   pg_catalog.pg_get_function_result(p.oid) as "Result data type",
   pg_catalog.pg_get_function_arguments(p.oid) as "Argument data types",
  CASE p.prokind
   WHEN 'a' THEN 'agg'
   WHEN 'w' THEN 'window'
   WHEN 'p' THEN 'proc'
   ELSE 'func'
  END as "Type"
FROM pg_catalog.pg_proc p
      LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace
WHERE p.proname OPERATOR(pg_catalog.~) '^(pg_catalog.day_inc)$'
   AND pg_catalog.pg_function_is_visible(p.oid)
ORDER BY 1, 2, 4;


> 
> 
> 
> --
> Sent from: https://www.postgresql-archive.org/PostgreSQL-general-f1843780.html
> 
> 
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com

your select returns no records but if I use 
WHERE p.proname ~ 'day_inc'
instead of 
WHERE p.proname OPERATOR(pg_catalog.~) '^(pg_catalog.day_inc)$'


      Schema
      Name
      Result data type
      Argument data types
      Type


      pg_catalog
      day_inc
      anyelement
      adate anyelement, ndays numeric
      func


      pg_catalog
      day_inc
      anyelement
      ndays numeric, adate anyelement
      func






--
Sent from: https://www.postgresql-archive.org/PostgreSQL-general-f1843780.html

On 9/30/19 7:28 AM, PegoraroF10 wrote:
> your select returns no records but if I use
> WHERE p.proname ~ 'day_inc'
> instead of
> WHERE p.proname OPERATOR(pg_catalog.~) '^(pg_catalog.day_inc)$'

Yeah I was not paying attention to what it was really looking for, the 
function name.

The list of extensions that you sent earlier are fairly common. I would 
not expect them to be contributing to the below otherwise there would 
have been more reports of what you are seeing.

 From the name of the functions and function arguments they look like 
something that is working with dates.

Does that bring anything to mind?

Do you have code you can grep for use of the functions?

> 
> 
>        Schema
>        Name
>        Result data type
>        Argument data types
>        Type
> 
> 
>        pg_catalog
>        day_inc
>        anyelement
>        adate anyelement, ndays numeric
>        func
> 
> 
>        pg_catalog
>        day_inc
>        anyelement
>        ndays numeric, adate anyelement
>        func



-- 
Adrian Klaver
adrian.klaver@aklaver.com

Adrian Klaver <adrian.klaver@aklaver.com> writes:
> The list of extensions that you sent earlier are fairly common. I would 
> not expect them to be contributing to the below otherwise there would 
> have been more reports of what you are seeing.

A quick "grep" shows that there is nothing named "day_inc" in core
Postgres nor any of the contrib extensions.  So this is something
homegrown.  It looks to me like somebody wanted a plus operator
that would add numerics and dates/timestamps/timestamptzs, and
figured they could be lazy and make one operator using "anyelement".
But this will capture numeric plus *anything*, so it was not a
good idea.  I'd recommend dropping the use of anyelement and just
making three non-polymorphic operators.

            regards, tom lane