- operator overloading not giving expected result

On 2022-07-05 13:52:32 +0530, Rajesh S wrote:

We are migrating our database from Oracle to Postgresql.  In oracle we have
used this syntax "SELECT ('1999-12-30'::DATE) - ('1999-12-11'::DATE)" to get
difference between two dates as a integer output (ex: 19).  But in Postgres the
same query returns result as "19 days".

Which PostgreSQL version is this?

I get 19 with PostgreSQL 11.16 and 14.0.

hp

--
_ | Peter J. Holzer | Story must make more sense than reality.
|_|_) | |
| | | hjp@hjp.at | -- Charles Stross, "Creative writing
__/ | http://www.hjp.at/ | challenge!"

Hi Rajesh:

On Fri, 8 Jul 2022 at 12:36, Rajesh S <rajesh.s@fincuro.com> wrote:

We are migrating our database from Oracle to Postgresql. In oracle we have used this syntax "SELECT ('1999-12-30'::DATE) - ('1999-12-11'::DATE)" to get difference between two dates as a integer output (ex: 19). But in Postgres the same query returns result as "19 days". Because of this we are getting errors while assigning this query output to a numeric variable saying "ERROR: invalid input syntax for type numeric: "1825 days"" and "ERROR: operator does not exist: interval + integer". To avoid changing the application code in many places to extract the number of days alone, we tried operator overloading concept as below.

Are you posting exact code? Because current doc states:

date - date → integer
Subtract dates, producing the number of days elapsed
date '2001-10-01' - date '2001-09-28' → 3

I remember it always working that way and 8.0 docs document it that
way too ( and I've used a lot of date arithmetic even in 7.x )

Maybe your EXACT code is not what you have pasted, but rather you
inserting dates in timestamp columns and then substracting said
columns? Because even your operations are defined in terms of
timestamptz, not dates.

Francisco Olarte.

## Rajesh S (rajesh.s@fincuro.com):

We are migrating our database from Oracle to Postgresql.  In oracle we
have used this syntax "SELECT ('1999-12-30'::DATE) -
('1999-12-11'::DATE)" to get difference between two dates as a integer
output (ex: 19).  But in Postgres the same query returns result as "19
days".

There's something fishy going on, as (date) - (date) returns integer
since a very long time (even the version 8.0 docs have that).
On the other hand, (timestamp) - (timestamp) gives an interval, so
first make sure you really got the data types right.

CREATE OR REPLACE FUNCTION public.dt_minus_dt(
    dt1 timestamp without time zone,
    dt2 timestamp without time zone)

See? There's TIMESTAMP, not DATE.

    LANGUAGE 'edbspl'

Is this really PostgreSQL or is it that fork - and if it's the fork,
does it behave the same way as stock PostgreSQL does? (I would be
surprised if that deviates in this place, but...).

    SELECT DATE_PART('day', dt1::timestamp - dt2::timestamp)::integer

And TIMESTAMP again.

Regards,
Christoph

--
Spare Space

Christoph Moench-Tegeder <cmt@burggraben.net> writes:

## Rajesh S (rajesh.s@fincuro.com):

We are migrating our database from Oracle to Postgresql.  In oracle we
have used this syntax "SELECT ('1999-12-30'::DATE) -
('1999-12-11'::DATE)" to get difference between two dates as a integer
output (ex: 19).  But in Postgres the same query returns result as "19
days".

There's something fishy going on, as (date) - (date) returns integer
since a very long time (even the version 8.0 docs have that).
On the other hand, (timestamp) - (timestamp) gives an interval, so
first make sure you really got the data types right.

It's easy to demonstrate that that query does indeed return "19"
in stock Postgres. Therefore, this is not stock Postgres.

    LANGUAGE 'edbspl'

Is this really PostgreSQL or is it that fork - and if it's the fork,
does it behave the same way as stock PostgreSQL does?

I'm no expert in this area, but my recollection is that Oracle's
type DATE is more nearly what we'd call TIMESTAMP (i.e., it includes
sub-day fields), and that EDB's fork follows Oracle's datatype naming.
So I'd not be at all surprised by this result if it's on EDB.

BTW, Oracle doesn't really accept "::" casting notation does it?
I always thought that was a pure Postgres-ism.

regards, tom lane

LANGUAGE 'edbspl'

This is the root cause of your issue.

You are not using PGSQL, but EDB version of it which is compatible with Oracle PL/SQL.

Hi All,

Thank you for your valuable comments and suggestions.  As it was pointed
out we are using EDB Postgres and the language was mentioned as
'edbspl'.  We made to work by setting the search_path variable as follows:

set search_path="$user", public, pg_catalog

And also changed the Operator overloading function as below. This gave
us the expected results.  Thanks once again for all your comments. 
Consider this thread as closed.

CREATE OR REPLACE FUNCTION public.dt_minus_dt(dt1 DATE, dt2 DATE)
RETURNS INTEGER as
$$
    SELECT (to_char(dt1::date,'J'::varchar) -
to_char(dt2::date,'J'::varchar))::integer
$$ LANGUAGE SQL IMMUTABLE STRICT PARALLEL SAFE COST 1;

CREATE OPERATOR public.- (
  PROCEDURE = dt_minus_dt,
  LEFTARG = DATE,
  RIGHTARG = DATE
);

Thanks,

Rajesh S

Hi,

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 LINE 1: ...lect LIEN_AC_NO from deposit_lien where
deposit_no='00021140...". I'm sharing the function and operator scripts
for your perusal. Please advise how to proceed.

CREATE OR REPLACE FUNCTION public.num_eq_varchar(
    numeric,
    varchar)
    RETURNS boolean
AS 'select $1::NUMERIC=CAST($2 AS numeric);'
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
);

CREATE OR REPLACE FUNCTION public.num_ne_varchar(
    numeric,
    varchar)
    RETURNS boolean
    LANGUAGE SQL IMMUTABLE
AS $BODY$
    select $1<>$2::numeric;
$BODY$;

-- Operator: <>;

-- DROP OPERATOR IF EXISTS public.<> (numeric , varchar);

CREATE OPERATOR public.<> (
    FUNCTION = num_ne_varchar,
    LEFTARG = numeric,
    RIGHTARG = varchar,
    COMMUTATOR = <>,
    NEGATOR = =,
    RESTRICT = neqsel,
    JOIN = neqjoinsel
);

Thanks,

Rajesh S

On Fri, Mar 15, 2024 at 6:26 AM Rajesh S <rajesh.s@fincuro.com> 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

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?

Thanks,

Rajesh S

The order of the arguments matter. You need an operator that expects a
varchar on the left hand side of the operator, and numeric on the right
side. For example:

create database cast_test;
\c cast_test
You are now connected to database "cast_test" as user "greg".

cast_test=# create table deposit_lien(deposit_no varchar);
CREATE TABLE

cast_test=# select * from deposit_lien where
deposit_no='0002114029832'::numeric;
ERROR: operator does not exist: character varying = numeric

cast_test=# create function varchar_eq_numeric(varchar,numeric)
cast_test-# returns bool language sql immutable as $$ select
$1::numeric=$2::numeric $$;
CREATE FUNCTION

cast_test=# create operator public.= (function = varchar_eq_numeric,leftarg
= varchar,rightarg = numeric);
CREATE OPERATOR

cast_test=# select * from deposit_lien where
deposit_no='0002114029832'::numeric;
deposit_no
------------
(0 rows)

But before creating the function and operator it was working fine

Was it? It helps to show us the exact things ran and the exact output,
rather than just "it was working fine" :)

Cheers,
Greg

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@fincuro.com> 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

--
Adrian Klaver
adrian.klaver@aklaver.com

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

Thanks,

Rajesh S

On 3/19/24 00:02, Rajesh S wrote:

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

1) Maybe you could explain the logic of taking a number and casting it
to a string to compare it to a number?

2) select 1::varchar = 1::varchar;
?column?
----------
t

So:

deposit_sub_no::varchar = 1::varchar

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

Thanks,

Rajesh S

--
Adrian Klaver
adrian.klaver@aklaver.com