Add 0 into array_agg if there is no matching data I wrote this view, and it is selecting almost everything I want, but I do not get results for all: project_keywords(which has_many project_report(keyword_id, project_id))

So a project_keyword has a data saved into project_report and the data is stored into position column but there are times when there is no data saved for a project_keyword.

When there is no data saved, I would like to add 0 into array_agg(pr.position).

 SELECT  
  k.name, 
  pr.project_id,
  array_agg(distinct pr.position) as pr_position,
  date(pr.created_at)
FROM 
  project_keywords pk
INNER JOIN keywords k on pk.keyword_id = k.id
INNER JOIN project_reports pr USING (project_id, keyword_id)
WHERE 
  date(pr.created_at) = '2014-03-27' AND pr.project_id = 2
GROUP BY k.name, pr.project_id, date(pr.created_at);


Also how can return results where array_agg(distinct pr.position) SIZE(no of elements) is > 1 

Edit:

SELECT  
  k.name, 
  pr.project_id,
  COALESCE(array_agg(DISTINCT pr.position), '{0}'::integer[]),
  date(pr.created_at)
FROM 
  project_keywords pk
INNER JOIN keywords k on pk.keyword_id = k.id
INNER JOIN project_reports pr USING (project_id, keyword_id)
WHERE 
  date(pr.created_at) = '2014-03-27' AND pr.project_id = 2
GROUP BY k.name, pr.project_id, date(pr.created_at)
HAVING array_length(COALESCE(array_agg(DISTINCT pr.position), '{0}'::integer[]), 1) &gt; 1;


This query is returning all the project_keywords which have project_reports row's, but they are not returning 0 for project_keywords which do not have associated rows in project_reports.
For the first point, you can try this:

COALESCE(array_agg(DISTINCT pr.position), '{0}'::integer[])


The same can be achieved by a CASE, too:

CASE WHEN array_agg(id) IS NULL
     THEN '{0}'::integer[] 
     ELSE array_agg(id) END


The second problem can be handled by a HAVING cause:

...
GROUP BY ...
HAVING array_length(COALESCE(array_agg(DISTINCT pr.position), '{0}'::integer[]), 1) &gt; 1;