Search code examples
sqlpostgresqlstring-aggregationlistagg

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?

Solution

  • 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"