CODEWITHSUNDEEP
postgresql
Danny Ellis Jr.
Danny Ellis Jr.

Reputation: 1706

Postgres string_agg function not recognized as aggregate function

I am attempting to run this query

SELECT  u.*, string_agg(CAST(uar.roleid AS VARCHAR(100)), ',') AS roleids, string_agg(CAST(r.role AS VARCHAR(100)), ',') AS systemroles
FROM idpro.users AS u 
INNER JOIN  idpro.userapplicationroles AS uar ON u.id = uar.userid 
INNER JOIN  idpro.roles AS r ON r.id = uar.roleid 
GROUP BY u.id, uar.applicationid
HAVING u.organizationid = '77777777-f892-4f4a-8328-c31df32bd6ba'
    AND uar.applicationid = 'd88fbf05-c048-4697-8bf3-036f39897183'
    AND (u.statusid = '7f9f0b75-44b7-4216-bf2a-03abc47dcff8') 
    AND uar.roleid IN ('cc9ada1c-fa21-400b-be98-c563ebb65a9c','de087148-4788-43da-89e2-dd7dff097735');

However, I'm getting an error stating that 

ERROR:  column "uar.roleid" must appear in the GROUP BY clause or be used in an aggregate function
LINE 9:  AND uar.roleid IN ('cc9ada1c-fa21-400b-be98-c563ebb65a9c','...

string_agg() IS an aggregate function, is it not? My intent, if it isn't obvious, is to return each user record with the roleids and rolenames in comma-delimited lists. If I am doing everything wrong, could you please point me in the right direction?

Upvotes: 1

Views: 1142

Answers (1)

JGH
JGH

Reputation: 17846

You are filtering the data, so a WHERE clause would be needed. This tutorial is worth reading.

SELECT  u.*, 
        string_agg(CAST(uar.roleid AS VARCHAR(100)), ',') AS roleids,
        string_agg(CAST(r.role AS VARCHAR(100)), ',') AS systemroles
FROM idpro.users AS u 
INNER JOIN  idpro.userapplicationroles AS uar ON u.id = uar.userid 
INNER JOIN  idpro.roles AS r ON r.id = uar.roleid 
WHERE u.organizationid = '77777777-f892-4f4a-8328-c31df32bd6ba'
    AND uar.applicationid = 'd88fbf05-c048-4697-8bf3-036f39897183'
    AND (u.statusid = '7f9f0b75-44b7-4216-bf2a-03abc47dcff8') 
    AND uar.roleid IN ('cc9ada1c-fa21-400b-be98-c563ebb65a9c','de087148-4788-43da-89e2-dd7dff097735');
GROUP BY u.id, uar.applicationid

The HAVING clause is helpful for filtering the aggregated values or the groups.

Since you are grouping by u.id, the table primary key you have access to every column of the u table. You can either use a where clause or a having clause.

For uar.applicationid, it is part of the group by so you can also use either a where or a having.

uar.roleid is not part of the group by clause, so to be usable in the having clause, you would have to consider the aggregated value.

The following example filters out rows whose aggregated length is more than 10 chars.

HAVING length(string_agg(CAST(uar.roleid AS VARCHAR(100)), ',')) > 10

A more common usage, on numerical field, is to filter out if the number of aggregated rows is less than a threshold (having count(*) > 2) or a sum of some kind (having sum(vacation_days) > 21)

Upvotes: 3

Related Questions