check if each value of array are equal

Question

I try to get d.id only if all rows with same id have same pday:

with dif_zero as (d.id, 
date_part('day', p.docdate)::text pday
from sh_billing.dogovor d 
join sh_billing.pays p on p.dogovor_id = d.id and p.is_actual
)
select id
from 
dif_zero 
group by id
having sum(char_sal_dif) = 0 
and avg(pday::int) = (array_agg(pday))[1]::int 
and avg(pday::int) = (array_agg(pday))[2]::int -- and so on...

Maby there is other way (more simple) to get to know if all elements in array equals to one another?

Or I can use window functioin in subquery, and after take only rows with value = 1.

select id, count(id) over(partition by id) one_day

Wich way is better? Or maby there is other way to do it more corrent?

• user1822: Why cast date_part to text just to convert it back to a number later? Your query also misses the char_sal_dif column
• Anatoliy D: @a_horse_with_no_name thanks for giving my attention to this! It's just historical reason, forgot to delete casting)

Answer 1

If you want to make sure that all days are the same, just compare the min and max. If they are the same, then all values are the same as well.

with dif_zero as (
 select d.id, 
 date_part('day', p.docdate)::int pday
 from sh_billing.dogovor d 
 join sh_billing.pays p on p.dogovor_id = d.id and p.is_actual
)
select id
from 
dif_zero 
group by id
having sum(char_sal_dif) = 0 -- where does char_sal_dif come from? 
 and min(pday) = max(pday) 

A more costly (=slower) but maybe easier to understand way, is to compare the count with the count of distinct values:

having count(*) = count(distinct pday)`

• Anatoliy D: Thanks for min max comparison! It's quite elegant!