Timestamp left outer join in postgresql

[flight]

Folks,

@castorp

Confused in this code.Left outer join in the timestamp filter ( A is Alias of Table A column & B is Alias of Table B column). Tried to migrate in Postgresql but not sure.

Oracle: 

AND TRUNC(A.TIMESTAMP,'mi') = TRUNC(B.TIMESTAMP(+), 'mi') 
 AND NVL(A.CONTACT_TYPE,'na') = NVL(B.CONTACT_TYPE(+), 'na')


Postgresql:

AND DATE_TRUNC('minute',A.TIMESTAMP::DATE) = DATE_TRUNC('minute',B.TIMESTAMP::DATE)
AND COALESCE(A.CONTACT_TYPE,'na') = COALESCE(B.CONTACT_TYPE, 'na')

[castorp]

Apparently that’s not an outer join: https://www.reddit.com/r/SQL/comments/111gzqh/comment/j8eo8rc to me that sounds as if it’s essentially a different way of writing a coalesce()

But: DATE_TRUNC('minute',A.TIMESTAMP::DATE) makes no sense in Postgres.

A date has no time part, so casting a timestamp to a date removes the time completely. Applying date_trunc() on a date then turns that back into a timestamptz but with the time set to 00:00:00

You probably want DATE_TRUNC('minute',A.TIMESTAMP) to keep the original time, but with the seconds set to zero

[pogomips]

@flight

Postgresql:

AND DATE_TRUNC('minute',A.TIMESTAMP**::DATE**)

You 've made the same mistake within another statement migration (older thread), I think. Maybe also the original one (oracle) was already somehow weird.
In postgres the “::” is a convenient way of type casting and used quite often. Don’t use it without care! In best case, you get an error while running, since the operator is not available for the given types. In the worst case you get a “silent”, implicit type cast, leading to unexpected effects.

It might be useful to try the expressions with some simple statements like:

select now(), now()::date;

or

select now(), now()::date; <myField>::date from <myTable> where ...