CODEWITHSUNDEEP
sqloracle-databasepostgresqlsyntax-errorstring-aggregation
coldhands
coldhands

Reputation: 457

converting oracle's listagg to postgres

I am trying to convert some Oracle queries to Postgres and came across listagg.

Oracle code

select max(eta) eta, mso_id, listagg(carrier_name, ',') 
within group (order by eta) as carrier 
from tb_flight_schedule group by mso_id;

I found out that Postgres's equivalent of listagg is string_agg and proceeded to swap the listagg with string_agg. However, I am encountering error (ERROR: function string_agg(character varying, unknown, date) does not exist). Am I missing something in the query? Postgres query is below:

select max(eta) eta, mso_id, string_agg(carrier_name, ',') 
WITHIN GROUP (ORDER BY eta) as carrier 
from tb_flight_schedule group by mso_id;

Upvotes: 1

Views: 3462

Answers (1)

Mureinik
Mureinik

Reputation: 311163

In Postgres, the within group clause is only used for orderd-set aggregate functions - i.e., functions that require an order by clause (e.g., the percentile functions). An optional order by clause, such as string_agg's, goes inside the function's arguments:

SELECT   MAX(eta) eta, mso_id, STRING_AGG(carrier_name, ',' ORDER BY eta) AS carrier 
FROM     tb_flight_schedule
GROUP BY mso_id;

Upvotes: 5

Related Questions