Re: Different results from identical matviews

Anders Steinlein <anders(at)e5r(dot)no> writes:
> On Thu, Jul 2, 2020 at 5:43 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> Unfortunately,
>> since the reverse-listing of this join is just going to say "USING
>> (email)", there's no way to detect from human-readable output that the
>> interpretation of the USING clauses is different. (We've contemplated
>> introducing not-SQL-standard syntax to allow flagging such cases, but
>> haven't pulled the trigger on that.)

> If I'm reading this correctly, would this be a "reason" to be more explicit
> when doing joins involving non-standard data types? I.e. would it be
> "safer" to do ON x1.email::citext == x2.email::citext instead of USING (if
> there is any difference at all...)?

Yes, it would be. Of course then you don't get the "merging" of the two
join output columns into one, so you might have to qualify references a
bit more.

You might find this thread interesting:

https://www.postgresql.org/message-id/flat/ffefc172-a487-aa87-a0e7-472bf29735c8%40gmail.com

regards, tom lane