Menu
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?
646
In order to concatenate field values, I would use “GROUP_CONCAT” function in Virtual DataPort Administration tool which is similar to LISTAGG function. For example, GROUP_CONCAT('<row separator>',<field_name>)
An Oracle LISTAGG Function is an aggregate function that returns a single row. This is used to transform data from multiple rows into a single list of values separated by a given delimiter. It operates on all rows and returns single. It returns a comma or other delimiter separatedresult set just like an excel CSV file.
Oracle database management systems, the main difference between these two databases is that PostgreSQL is an open-source database, while Oracle is a closed database system. PostgreSQL is a free relational object-oriented database management system that is developed by volunteer developers worldwide.
One of the biggest advantages of choosing the EnterpriseDB version of PostgreSQL is its Oracle compatibility.
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" If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
