Re: - operator overloading not giving expected result

Christoph Moench-Tegeder <cmt(at)burggraben(dot)net> writes:
> ## Rajesh S (rajesh(dot)s(at)fincuro(dot)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