1

I have a polygon layer table in PostgreSQL and I want to create a simple query using GROUP BY .

Here my fields :

fid   field1
--    ----
1     text1
1     text2
1     text3
2     text1
3     text4
4     text10
8     text1
1     text15
9     text20
9     text20

I want to use a PostGIS spatial query to group by my layer with fid field but i need to keep all unique values from field1

Here an example where i want for export after query :

fid   field1
--    ----
1     text1,text2,text3
2     text1
3     text4
4     text10
8     text1
1     text15
9     text20

i try this Query :

SELECT fid,field1 FROM public.mytable GROUP BY fid

but I don't know how to keep all unique values from field1.

Any idea ?

1
  • Pure SQL questions without geospatial content are more appropriately asked over in Database Administrators. – Vince Sep 22 '17 at 23:22
1

You need an aggregate function. In this cas string_agg() should do it. First argument is the field to aggregate, second argument is the delimiter.

SELECT a.fid, string_agg(a.field1, ', ')
FROM 
   (SELECT DISTINCT b.fid, b.field1 FROM public.mytable AS b) AS a

GROUP BY a.fid;

This is generic to PostgreSQL, not just PostGIS.

8
  • that keep unique values from field 1 ? – jessie jes Sep 22 '17 at 22:14
  • It will aggregate every value in field1 that has a common value in fid column. – RoperMaps Sep 22 '17 at 22:16
  • yes but i need unique values and not duplicate like this : 1 text1,text2,text1 i need like this 1 text1,text2 in this case – jessie jes Sep 22 '17 at 22:19
  • I've updated my answer. Not texted or though. Basically build a sub table eliminating any duplicates, and then query that. There may be faster ways. – RoperMaps Sep 22 '17 at 22:53
  • 1
    Why not just str_agg(DISTINCT a.field1, ', ' ) instead of subquery? – pLumo Sep 25 '17 at 8:36
0
SELECT fid, string_agg(field1, ', ')
FROM public.mytable
GROUP BY fid
3
  • that keep unique values from field 1 ? – jessie jes Sep 22 '17 at 22:14
  • yes did you try? – bertt Sep 22 '17 at 22:26
  • not yet but tutorial say i think say string_agg keep all values right ? – jessie jes Sep 22 '17 at 22:28

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