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,
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

  • Is there a column named p in the session table? – Clodoaldo May 30 at 14:20
  • no, p is an alias to coalesce select distinct on (p) coalesce(native_fcm_token, push_token) p – Belegnar May 30 at 15:22

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;


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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.