Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

First have a look at this question on StackOverflow.

I'm looking to accomplish the same task, except I also need to add an aggregate function (PostGIS's ST_Union) to my query.

How can I combine the answer using DISTINCT with an aggregation...

I've tried:

SELECT DISTINCT ON (name, zonedistrict_id)
       ST_Union(geom) as geom, gid, name, zonedistrict_id, zonestyle_id, longname 
FROM zones
ORDER BY name, zonedistrict_id, zonestyle_id;

Which screams:

column "zones.gid" must appear in the GROUP BY clause or be used in an aggregate function

Which is strange, because if I remove ST_Union(geom) as geom,, the query works. But it's absent of the union'd geometry.

share|improve this question

1 Answer 1

up vote 4 down vote accepted

Answer to question

SELECT DISTINCT ON (name, zonedistrict_id)
       ST_Union(geom) as geom, gid, name, zonedistrict_id, zonestyle_id, longname 
FROM   zones
GROUP BY gid, name, zonedistrict_id, zonestyle_id, longname
ORDER  BY name, zonedistrict_id, zonestyle_id;

It depends on what you are actually trying to achieve and what version of Postgres you are using and the table definition of the underlying table. You chose not to disclose any of that.

This gives you one distinct row per (name, zonedistrict_id), but geom is only the aggregate over (gid, name, zonedistrict_id, zonestyle_id, longname)

In older versions you had to list every non-aggregated column of the SELECT list in the GROUP BY list. This changed somewhat with PostgreSQL 9.1. I quote the release notes:

Allow non-GROUP BY columns in the query target list when the primary key is specified in the GROUP BY clause (Peter Eisentraut)

My guess

However, I suspect you secretly want this:

SELECT DISTINCT ON (name, zonedistrict_id)
       ST_Union(geom) OVER (PARTITION BY name, zonedistrict_id) AS geom
      ,gid, name, zonedistrict_id, zonestyle_id, longname 
FROM   zones
ORDER  BY name, zonedistrict_id, zonestyle_id;

Using a ST_Union(geom) as aggregate window function. So geom is actually the aggregate over (name, zonedistrict_id), which seems more likely.

This should be possible since the PostGis manual tells us about its aggregate functions:

... can be used just like any other sql aggregate function such as sum, average.

And sql aggregate functions can be used as window functions.

share|improve this answer
1  
You secretly guessed right. This is exactly what I needed. Thank you! Not only did this solve my problem, it also forced me to move my Postgres data onto a new AWS EBS because initially I was getting ERROR: could not write block n of temporary file: No space left on device. I reserved 25 GB, moved Postgres onto it (with XFS) and query returned successfully. Thx! –  Jordan Arseno May 6 '13 at 4:27

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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