Horton

Thread

  1. BUG #15445: Difference between two dates is not an integer

    PG Bug reporting form <noreply@postgresql.org> — 2018-10-19T13:14:47Z

    The following bug has been logged on the website:

Bug reference:      15445
Logged by:          Martin Varady
Email address:      martin.varady@gmail.com
PostgreSQL version: 10.4
Operating system:   Windows Server 2012
Description:        

I've been using Postgres 9.3 and am recently testing moving to Postgres 10.
Testing Postgres 10 I've noticed that the difference between two dates gives
me an interval when it is documented as giving a integer. This is how it
worked in Postgres 9.3.

From Documentation of Postgres 10:
https://www.postgresql.org/docs/10/static/functions-datetime.html
date '2001-10-01' - date '2001-09-28' 	integer '3' (days)

Test Case:
I'm casting the result to an int to prove my point.
select (date '2001-10-01' - date '2001-09-28')::int
In Postgres 9.3 provides a result of 3 Integer type
In Postgres 10 gives error:

ERROR:  cannot cast type interval to integer
LINE 1: select (date '2001-10-01' - date '2001-09-28')::int
                                                      ^
SQL state: 42846
Character: 47

  2. Re: BUG #15445: Difference between two dates is not an integer

    Sergei Kornilov <sk@zsrv.org> — 2018-10-19T13:26:27Z

    Hello

Works for me in pg 10.5, 11.0, and 9.6

Try this query:
SELECT n.nspname as "Schema",
  o.oprname AS "Name",
  CASE WHEN o.oprkind='l' THEN NULL ELSE pg_catalog.format_type(o.oprleft, NULL) END AS "Left arg type",
  CASE WHEN o.oprkind='r' THEN NULL ELSE pg_catalog.format_type(o.oprright, NULL) END AS "Right arg type",
  pg_catalog.format_type(o.oprresult, NULL) AS "Result type",
  coalesce(pg_catalog.obj_description(o.oid, 'pg_operator'),
           pg_catalog.obj_description(o.oprcode, 'pg_proc')) AS "Description"
FROM pg_catalog.pg_operator o
     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = o.oprnamespace
WHERE o.oprname OPERATOR(pg_catalog.=) '-'
  AND o.oprleft OPERATOR(pg_catalog.=) (select oid from pg_type where typname OPERATOR(pg_catalog.=) 'date')
  AND o.oprleft OPERATOR(pg_catalog.=) o.oprright
  AND pg_catalog.pg_operator_is_visible(o.oid)
ORDER BY 1, 2, 3, 4;

Maybe you have custom operators?

regards, Sergei

  3. Re: BUG #15445: Difference between two dates is not an integer

    Martin Varady <martin.varady@gmail.com> — 2018-10-19T14:05:46Z

    I got what you would expect but still doesn't work. It is the enterpriseDB
version we bought for Oracle to Postgres conversions.
"EnterpriseDB 10.4.9, compiled by Visual C++ build 1800, 64-bit"

I'll keep looking at it to see if I can figure it out. Thanks.

[image: image.png]

On Fri, Oct 19, 2018 at 9:26 AM Sergei Kornilov <sk@zsrv.org> wrote:

> Hello
>
> Works for me in pg 10.5, 11.0, and 9.6
>
> Try this query:
> SELECT n.nspname as "Schema",
>   o.oprname AS "Name",
>   CASE WHEN o.oprkind='l' THEN NULL ELSE pg_catalog.format_type(o.oprleft,
> NULL) END AS "Left arg type",
>   CASE WHEN o.oprkind='r' THEN NULL ELSE
> pg_catalog.format_type(o.oprright, NULL) END AS "Right arg type",
>   pg_catalog.format_type(o.oprresult, NULL) AS "Result type",
>   coalesce(pg_catalog.obj_description(o.oid, 'pg_operator'),
>            pg_catalog.obj_description(o.oprcode, 'pg_proc')) AS
> "Description"
> FROM pg_catalog.pg_operator o
>      LEFT JOIN pg_catalog.pg_namespace n ON n.oid = o.oprnamespace
> WHERE o.oprname OPERATOR(pg_catalog.=) '-'
>   AND o.oprleft OPERATOR(pg_catalog.=) (select oid from pg_type where
> typname OPERATOR(pg_catalog.=) 'date')
>   AND o.oprleft OPERATOR(pg_catalog.=) o.oprright
>   AND pg_catalog.pg_operator_is_visible(o.oid)
> ORDER BY 1, 2, 3, 4;
>
> Maybe you have custom operators?
>
> regards, Sergei
>

  4. Re: BUG #15445: Difference between two dates is not an integer

    Tom Lane <tgl@sss.pgh.pa.us> — 2018-10-19T14:26:08Z

    Martin Varady <martin.varady@gmail.com> writes:
> I got what you would expect but still doesn't work. It is the enterpriseDB
> version we bought for Oracle to Postgres conversions.
> "EnterpriseDB 10.4.9, compiled by Visual C++ build 1800, 64-bit"

Well, you should have a word with EDB then, but what it sounds like
is they install a nondefault date - date operator, or possibly remove
PG's standard one so that timestamp - timestamp gets chosen instead.
Which is probably reasonable if your goal is compatibility with
Oracle.

			regards, tom lane

  5. Re: BUG #15445: Difference between two dates is not an integer

    Martin Varady <martin.varady@gmail.com> — 2018-10-19T14:37:22Z

    Not sure how to close my logged bug. But its safe to say its not a defect.
Thank You for your help.

On Fri, Oct 19, 2018 at 10:26 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:

> Martin Varady <martin.varady@gmail.com> writes:
> > I got what you would expect but still doesn't work. It is the
> enterpriseDB
> > version we bought for Oracle to Postgres conversions.
> > "EnterpriseDB 10.4.9, compiled by Visual C++ build 1800, 64-bit"
>
> Well, you should have a word with EDB then, but what it sounds like
> is they install a nondefault date - date operator, or possibly remove
> PG's standard one so that timestamp - timestamp gets chosen instead.
> Which is probably reasonable if your goal is compatibility with
> Oracle.
>
>                         regards, tom lane
>

  6. Re: BUG #15445: Difference between two dates is not an integer

    Anthony Sotolongo <asotolongo@gmail.com> — 2018-10-19T15:02:58Z

    Hi Martin if you are using EDB Postgres this operation require set
edb_redwood_date = false

And then the operator - with date Will be like PostgreSQL native


Regards


El vie., 19 de oct. de 2018 11:56 a.m., Martin Varady <
martin.varady@gmail.com> escribió:

> Not sure how to close my logged bug. But its safe to say its not a defect.
> Thank You for your help.
>
> On Fri, Oct 19, 2018 at 10:26 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
>> Martin Varady <martin.varady@gmail.com> writes:
>> > I got what you would expect but still doesn't work. It is the
>> enterpriseDB
>> > version we bought for Oracle to Postgres conversions.
>> > "EnterpriseDB 10.4.9, compiled by Visual C++ build 1800, 64-bit"
>>
>> Well, you should have a word with EDB then, but what it sounds like
>> is they install a nondefault date - date operator, or possibly remove
>> PG's standard one so that timestamp - timestamp gets chosen instead.
>> Which is probably reasonable if your goal is compatibility with
>> Oracle.
>>
>>                         regards, tom lane
>>
>

  7. Re: BUG #15445: Difference between two dates is not an integer

    Martin Varady <martin.varady@gmail.com> — 2018-10-19T15:52:59Z

    That was it, thank you so much. I'll check out what the impact of that is
during the migration process. Worse case I set it after the fact.

Thank You,
Martin

On Fri, Oct 19, 2018 at 11:03 AM Anthony Sotolongo <asotolongo@gmail.com>
wrote:

> Hi Martin if you are using EDB Postgres this operation require set
> edb_redwood_date = false
>
> And then the operator - with date Will be like PostgreSQL native
>
>
> Regards
>
>
> El vie., 19 de oct. de 2018 11:56 a.m., Martin Varady <
> martin.varady@gmail.com> escribió:
>
>> Not sure how to close my logged bug. But its safe to say its not a
>> defect. Thank You for your help.
>>
>> On Fri, Oct 19, 2018 at 10:26 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>>
>>> Martin Varady <martin.varady@gmail.com> writes:
>>> > I got what you would expect but still doesn't work. It is the
>>> enterpriseDB
>>> > version we bought for Oracle to Postgres conversions.
>>> > "EnterpriseDB 10.4.9, compiled by Visual C++ build 1800, 64-bit"
>>>
>>> Well, you should have a word with EDB then, but what it sounds like
>>> is they install a nondefault date - date operator, or possibly remove
>>> PG's standard one so that timestamp - timestamp gets chosen instead.
>>> Which is probably reasonable if your goal is compatibility with
>>> Oracle.
>>>
>>>                         regards, tom lane
>>>
>>