Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to apply a function to each element of an array column in Postgres?

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.

like image 775
punkish Avatar asked Dec 21 '11 01:12

punkish


People also ask

What does the PostgreSQL Unnest () function do?

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.


1 Answers

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) 
like image 138
Joey Adams Avatar answered Sep 29 '22 05:09

Joey Adams