|
Sorry Adrian, my bad. I'd mentioned "deposit_sub_no='1'" by
mistake, actually it was "deposit_sub_no=cast(1 as varchar)".
This was throwing error "SQL Error [42883]: ERROR: operator does
not exist: numeric = character varying Hint: No operator matches
the given name and argument types. You might need to add explicit
type casts. Position: 19". Then realized that "numeric=text"
works but "numeric=varchar" does not. I could resolve the problem
by creating the following function and operator by commenting
"CUMMUTATOR" and "NEGATOR". Also the error "operator is only a
shell" also vanished. I'm just sharing the script for your
reference. Thank you very much for your valuable support.
CREATE OR REPLACE FUNCTION public.num_eq_varchar(
numeric,
varchar)
RETURNS boolean
AS 'select case when $2 ~ ''^[0-9\.]+$'' then $1
operator(pg_catalog.=) cast($2 as numeric) else $1::varchar = $2
end;'
LANGUAGE SQL IMMUTABLE;
-- Operator: =;
-- DROP OPERATOR IF EXISTS public.= (numeric , varchar);
CREATE OPERATOR public.= (
FUNCTION = num_eq_varchar,
LEFTARG = numeric,
RIGHTARG = varchar,
-- COMMUTATOR = =,
-- NEGATOR = <>,
RESTRICT = eqsel,
JOIN = eqjoinsel,
HASHES, MERGES
);
On 18-03-2024 21:20, Adrian Klaver
wrote:
On
3/18/24 00:05, Rajesh S wrote:
Thank you for your response. Actually, I
was trying to address the following query.
select LIEN_AC_NO from deposit_lien where
deposit_no='0002114029832' and deposit_sub_no='1' and unlien_dt
is null and unlien_remarks is null;
In the above query "deposit_sub_no" column is "numeric" type and
passing '1' (as varchar). To address this I'd created the
function and operator as I'd mentioned in the earlier mail.
Even the following query throws error after creating the
function and operator.
select * from deposit_lien where deposit_no='0002114029832';
ERROR: operator is only a shell: character varying = numeric
LINE 1: select * from deposit_lien where
deposit_no='0002114029832' ^ SQL state: 42883 Character: 44
In the above query "deposit_no" column is having "varchar" data
type. But before creating the function and operator it was
working fine. Tried dropping the same, even though the same
error. How to proceed now?
Not clear to me what the problem is you are trying to solve?
On a stock Postgres install:
select 1::numeric = '1';
?column?
----------
t
select '0002114029832'::varchar = '0002114029832';
?column?
----------
t
Thanks,
Rajesh S
On 15-03-2024 19:10, Greg Sabino Mullane wrote:
On Fri, Mar 15, 2024 at 6:26 AM Rajesh S
<rajesh.s@xxxxxxxxxxx> wrote:
I wanted to implement a new "=" (equal) operator with
LEFTARG as
numeric and RIGHTARG as varchar. But after creating the
function
and operator, psql shows the error "operator is only a
shell:
character varying = numeric
Your operator has numeric on the left and varchar on the
right. But your query is doing numeric on the RIGHT. Probably
want to make a matching one to cover both cases.
Cheers,
Greg
|
