i've defined the following SQL statement in the query editor of qgis' db manager:

select sd.fk_polyg, count(sd.fk_polyg), STRING_AGG(DISTINCT sl.typ, ', ') as concat_typ
from schaden_stein_detail as sd 
join schaden_lookup as sl on  sd.s_id =  sl.id
group by sd.fk_polyg
order by STRING_AGG(DISTINCT sl.typ, ', ') ; 

there is one single polygon dataset (which is not part of that sql-statement). that dataset is referenced in the FROM part: "schaden_stein_detail"-table (i.e. damage details) by a foreign key

the code groups damage details from the table "schaden_stein_detail"by the foreign key field "fk_polyg" and concatenates any existing detail entry into a text field aliased as "concat_type" (where in case of double entries just one single value is returned by utilizing the DISTINCT parameter). remark: the join-part is just for accessing the text-values of the damage types that are stored in the lookup-table "schaden_lookup" / field "sl.typ"

the statement works fine in qgis db managers SQL editor. but it returns a "no such function: STRING_AGG" in "Data Source Manager / Virtual Layer" (of course the schema-names of the above SQL-statements are not used in the virtual layer definition!)

I thought STRING_AGG was a standard PostgreSQL function. any ideas about that?

2 Answers 2


Virtual layers don't use the underlying storage provider(s) but rather sqlite/spatialite, so postgres specific functions are not available.

You can instead create a query layer from the DB Manager, or you can look for an SQLite compatible function.

enter image description here


From @JGH’s answer, the function i was looking for following his SQLite-remark was GROUP_CONCAT(). that function is basically the same as STRING_AGG() in PostgreSQL. with one exception: I have used STRING_AGG(DISTINCT sl.typ, ', ') in my postgres-statement, which lets me reduce recurring sets of sl.typ-combinations to one single set within this short statement.

SQLite throws an error when using GROUP_CONCAT() in the same way and needes a workaround like this one with a sub-select & the DISTINCT within that sub-select :

SELECT sub.fk_polyg, group_concat(sub.typ, ', ') as concat_typ FROM 
  (select  sd.fk_polyg, sl.typ  
   from schaden_stein_detail as sd 
   join schaden_lookup as sl on  sd.s_id =  sl.id
   order BY sl.typ) sub
GROUP BY sub.fk_polyg;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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