postgresql - How to use DISTINCT in VIEWS correctly -

i have 2 tables want make join columns provide view java/hibernate application. looks this:

create view customer_contacts cc select distinct on (cust.id) cust.id   cust.company   cust.zip   ...   con.name   con.forename   ... contacts con left join customer cust on con.customer = cust.id order cust.id 

so far good. simple. if make select on view like:

select *  cc name ilike '%schult%' 

i 13 results.

if make same query directly view statement

select distinct on (cust.id) cust.id   cust.company   cust.zip   ...   con.name   con.forename   ... contacts con left join customer cust on con.customer = cust.id name ilike '%schult%' order cust.id 

i got 75 results! figured out distinct corrupts result. why?

and how can use correctly?

your queries (view based , direct) have different order of applying condition:

• direct query searches %shult% , applies distinct on
• view applies distinct on , searches %shult%

are aware how distinct on works? selects first row (it may undeterministic if proper sort not defined) given attributes , leaves other.

for instance:

let's have customer id=1 , 2 connected contacts 1 name='schultz' , 1 name='schmidt'. view based select apply distinct on , select customer contact (first one, undeterministic in case), name ilike '%schult%' applied - may happen schultz removed distinct on.

---

recommended reading:

https://www.postgresql.org/docs/9.0/static/sql-select.html#sql-distinct