A Pg query returns an array. I would like to retrieve that with each element formatted to 3 decimal places. How can I apply a function to each element of an array? Something like the following (wrong, obviously) --
SELECT Round(ARRAY[1.53224,0.23411234], 2); {1.532, 0.234}
I guess I am looking for something like Perl's map
function.
PostgreSQL unnest is the type of array functions; the unnest function in PostgreSQL is basically used to expand the array into rows. Unnest function is converting an array into a table-like structure; we can also generate a table structure of an array using unnest function in PostgreSQL.
First, turn the array into a set using unnest:
> SELECT n FROM unnest(ARRAY[1.53224,0.23411234]) AS n; n ------------ 1.53224 0.23411234 (2 rows)
Then, apply an expression to the column:
> SELECT ROUND(n, 2) FROM unnest(ARRAY[1.53224,0.23411234]) AS n; round ------- 1.53 0.23 (2 rows)
Finally, use array_agg to turn the set back into an array:
> SELECT array_agg(ROUND(n, 2)) FROM unnest(ARRAY[1.53224,0.23411234]) AS n; array_agg ------------- {1.53,0.23} (1 row)
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