Categories
listagg postgresql sql string-aggregation

What’s the equivalent for LISTAGG (Oracle database) in PostgreSQL?

I have to replace the Oracle driver with the newest PostgreSQL. PostgreSQL doesn’t know the function LISTAGG. I have to concat values by comma separated. What’s the equivalent for the Oracle’s function LISTAGG in PostgreSQL?

The equivalent function in PostgreSQL is STRING_AGG()

SELECT STRING_AGG (column_name,', ') 
FROM my_table

string_agg : input values concatenated into a string, separated by delimiter

For example, get list of all agreement_id then represent it in a string, in Apache Ofbiz 17.12.04

SELECT STRING_AGG(agreement_id, ', ') FROM agreement_item;
-- result
-- "8000, DS-1000-SALES, DS-1000-PURCH, 9000, AGR_SALES"