Postgresql – Different results from select and from materialized view

materialized-viewpostgresql

select distinct on (p) coalesce(native_fcm_token, push_token) p
from session
where user_id = 2779
and ((push_token is not null) or (native_fcm_token is not null))

returns 27 rows

refresh materialized view session_push_token
select * from session_push_token where user_id = 2779

returns 22 rows

session_push_token is defined as

create materialized view session_push_token as
SELECT DISTINCT ON (COALESCE(s.native_fcm_token, s.push_token)) COALESCE(s.native_fcm_token, s.push_token) AS push_token,
                                                                s.os,
                                                                s.user_id
FROM session s
WHERE ((s.push_token IS NOT NULL) OR (s.native_fcm_token IS NOT NULL));

How this can happend?
Postgresql 11 from docker image

Best Answer

They give different results because they do different things.

One does the "distinct on" over only those rows which meet user_id = 2779, the other returns only those those rows where the result of the "distinct on" happens to have user_id = 2779.

So if one group of the values yielded by expression "p" has a row where user_id = 2779 but also other rows, and the row where user_id = 2779 is not the one selected to represent the group, then no row for that group is returned. Since you did not specify an ORDER BY, it is indeterminate which row is selected to represent each group.

You can reproduce this without a materialized view, it is the difference between:

select * from (<query> where user_id=2779) foobar;

and 

select * from (<query>) foobar where user_id=2779;
Related Question