I'm new to PostgreSQL and I have a problem migrating databases from Oracle to PostgreSQL 9.2. I must change some native queries in my code. I have a subquery like:
SELECT wm_concat(p.first_name || ' ' || p.last_name)
FROM TODO_PERSON PR
JOIN PERSON P ON (p.id == pr.person_id)
WHERE todo_id = internal_table.id
In PostgreSQL I can't find equivalent function or other solutions ...
PostgreSQL has a bunch of available aggregation functions.
wm_concat equivalent seems to be string_agg, except you must specify the delimiter. Please, check the documentation for details.
The equivalent is string_agg.
i.e.: "show me the list of the orders for each customer"
SELECT customer_id, string_agg(order_id, ',')
FROM order
GROUP BY customer_id
It's just simple concatenation. You can either use CONCAT function or write the query just with pipes;
So first solution would be:
SELECT CONCAT(p.first_name, ' ', p.last_name) [...]
or:
SELECT p.first_name || ' ' || p.last_name [...]
If you want to add commas to the results just add commas instead of gap, as a string between column names. i.e.:
SELECT CONCAT(p.first_name, ', ', p.last_name) [...]
The very last part of your query is quite mysterious. What are you using those brackets for?
