Thread: - operator overloading not giving expected result

Hi,

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.

CREATE OR REPLACE FUNCTION public.dt_minus_dt(
    dt1 timestamp without time zone,
    dt2 timestamp without time zone)
    RETURNS integer
    LANGUAGE 'edbspl'
    COST 100
    VOLATILE SECURITY DEFINER PARALLEL UNSAFE
AS $BODY$
    days INTEGER;
BEGIN
    SELECT DATE_PART('day', dt1::timestamp - dt2::timestamp)::integer INTO days;
    RETURN days;
END
$BODY$;

CREATE OPERATOR public.- (
    FUNCTION = public.dt_minus_dt,
    LEFTARG = timestamp without time zone,
    RIGHTARG = timestamp without time zone
);

When we execute "SELECT ('1999-12-30'::DATE) - ('1999-12-11'::DATE)", we are still getting "19 days" as result and not "19" as we expect.  The above same function works as expected for the operator + or ===. 

CREATE OPERATOR public.+ (
    FUNCTION = public.dt_minus_dt,
    LEFTARG = timestamp without time zone,
    RIGHTARG = timestamp without time zone
);

SELECT ('1999-12-30'::DATE) + ('1999-12-11'::DATE)

CREATE OPERATOR public.=== (
    FUNCTION = public.dt_minus_dt,
    LEFTARG = timestamp without time zone,
    RIGHTARG = timestamp without time zone
);

SELECT ('1999-12-30'::DATE) === ('1999-12-11'::DATE)

I really appreciate anyone's help in resolving this case.  Thanks in advance.

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
Postgresthe same query returns result as "19 days".  Because of this we are getting errors while assigning this query
outputto a numeric variable saying "ERROR: invalid input syntax for type numeric: "1825 days"" and "ERROR: operator
doesnot exist: interval + integer".  To avoid changing the application code in many places to extract the number of
daysalone, 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
);

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,

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

Hi,

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.

CREATE OR REPLACE FUNCTION public.dt_minus_dt(
    dt1 timestamp without time zone,
    dt2 timestamp without time zone)
    RETURNS integer
    LANGUAGE 'edbspl'
    COST 100
    VOLATILE SECURITY DEFINER PARALLEL UNSAFE
AS $BODY$
    days INTEGER;
BEGIN
    SELECT DATE_PART('day', dt1::timestamp - dt2::timestamp)::integer INTO days;
    RETURN days;
END
$BODY$;

CREATE OPERATOR public.- (
    FUNCTION = public.dt_minus_dt,
    LEFTARG = timestamp without time zone,
    RIGHTARG = timestamp without time zone
);

When we execute "SELECT ('1999-12-30'::DATE) - ('1999-12-11'::DATE)", we are still getting "19 days" as result and not "19" as we expect.  The above same function works as expected for the operator + or ===. 

CREATE OPERATOR public.+ (
    FUNCTION = public.dt_minus_dt,
    LEFTARG = timestamp without time zone,
    RIGHTARG = timestamp without time zone
);

SELECT ('1999-12-30'::DATE) + ('1999-12-11'::DATE)

CREATE OPERATOR public.=== (
    FUNCTION = public.dt_minus_dt,
    LEFTARG = timestamp without time zone,
    RIGHTARG = timestamp without time zone
);

SELECT ('1999-12-30'::DATE) === ('1999-12-11'::DATE)

I really appreciate anyone's help in resolving this case.  Thanks in advance.

Rajesh S

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?

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

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

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

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