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.
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
Em ter, 24 de set de 2019 às 10:35, PegoraroF10 <marcos@f10.com.br>
escreveu:
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.
Witch version are you using? I tried it against current master and
everything is ok:
fabrizio=# SELECT 1::NUMERIC + 1::BIGINT;
?column?
----------
2
(1 row)
This error means I have more than one way to calculate that formula ?
Did I create that operator erroneously ?
Did you created an operator for it? Why?
Regards,
--
Fabrízio de Royes Mello Timbira - http://www.timbira.com.br/
PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento
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
Em ter, 24 de set de 2019 às 10:52, PegoraroF10 <marcos@f10.com.br>
escreveu:
I don´t know if I did.
I think you 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
Take a look:
postgres=# SELECT version();
version
----------------------------------------------------------------------------------------------------------------------------------
PostgreSQL 11.5 (Debian 11.5-1.pgdg90+1) on x86_64-pc-linux-gnu, compiled
by gcc (Debian 6.3.0-18+deb9u1) 6.3.0 20170516, 64-bit
(1 row)
postgres=# SELECT 1::NUMERIC + 1::BIGINT;
?column?
----------
2
(1 row)
And looking at the catalog:
postgres=# SELECT pg_typeof(1::NUMERIC + 1::BIGINT);
pg_typeof
-----------
numeric
(1 row)
postgres=# SELECT * FROM pg_operator WHERE oid = '+(numeric,
numeric)'::regoperator;
oprname | oprnamespace | oprowner | oprkind | oprcanmerge | oprcanhash |
oprleft | oprright | oprresult | oprcom | oprnegate | oprcode | oprrest
| oprjoin
---------+--------------+----------+---------+-------------+------------+---------+----------+-----------+--------+-----------+-------------+---------+---------
+ | 11 | 10 | b | f | f |
1700 | 1700 | 1700 | 1758 | 0 | numeric_add | - |
-
(1 row)Please, try it in your environment and let us know.
Regards,
--
Fabrízio de Royes Mello Timbira - http://www.timbira.com.br/
PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento
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
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;
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: https://www.postgresql-archive.org/PostgreSQL-general-f1843780.html
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
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
--
Sent from: https://www.postgresql-archive.org/PostgreSQL-general-f1843780.html
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
oprname oprkind oprleft oprright oprresult oprcode
+ l 0 20 20 int8up
+ l 0 21 21 int2up
+ l 0 23 23 int4up
+ l 0 700 700 float4up
+ l 0 701 701 float8up
+ l 0 1700 1700 numeric_uplus
+ b 20 20 20 int8pl
+ b 20 21 20 int82pl
+ b 20 23 20 int84pl
+ b 20 869 869 int8pl_inet
+ b 21 20 20 int28pl
+ b 21 21 21 int2pl
+ b 21 23 23 int24pl
+ b 23 20 20 int48pl
+ b 23 21 23 int42pl
+ b 23 23 23 int4pl
+ b 23 1082 1082 integer_pl_date
+ b 600 600 600 point_add
+ b 602 600 602 path_add_pt
+ b 602 602 602 path_add
+ b 603 600 603 box_add
+ b 700 700 700 float4pl
+ b 700 701 701 float48pl
+ b 701 700 701 float84pl
+ b 701 701 701 float8pl
+ b 702 703 702 timepl
+ b 718 600 718 circle_add_pt
+ b 790 790 790 cash_pl
+ b 869 20 869 inetpl
+ b 1034 1033 1034 aclinsert
+ b 1082 23 1082 date_pli
+ b 1082 1083 1114 datetime_pl
+ b 1082 1186 1114 date_pl_interval
+ b 1082 1266 1184 datetimetz_pl
+ b 1083 1082 1114 timedate_pl
+ b 1083 1186 1083 time_pl_interval
+ b 1114 1186 1114 timestamp_pl_interval
+ b 1184 1186 1184 timestamptz_pl_interval
+ b 1186 1082 1114 interval_pl_date
+ b 1186 1083 1083 interval_pl_time
+ b 1186 1114 1114 interval_pl_timestamp
+ b 1186 1184 1184 interval_pl_timestamptz
+ b 1186 1186 1186 interval_pl
+ b 1186 1266 1266 interval_pl_timetz
+ b 1266 1082 1184 timetzdate_pl
+ b 1266 1186 1266 timetz_pl_interval
+ b 1700 1700 1700 numeric_add
+ b 1700 2283 2283 pg_catalog.day_inc
+ b 2283 1700 2283 pg_catalog.day_inc
+ b 3831 3831 3831 range_union--
Sent from: https://www.postgresql-archive.org/PostgreSQL-general-f1843780.html
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
Typepg_catalog
day_inc
anyelement
adate anyelement, ndays numeric
funcpg_catalog
day_inc
anyelement
ndays numeric, adate anyelement
func
--
Adrian Klaver
adrian.klaver@aklaver.com