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
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:
Show quoted text
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
Attachments:
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
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:
Show quoted text
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
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ó:
Show quoted text
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
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:
Show quoted text
Hi Martin if you are using EDB Postgres this operation require set
edb_redwood_date = falseAnd 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