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

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

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

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