I am trying to truncate value in an array of integer to 8 decimals in Postgres in a SQL SELECT statement and get back the cleaned array.
[1.030411088488374,1.0985247734044379,1.0863039797613594]
-
What is the result you expect?user330315– user3303152019-04-15 13:58:31 +00:00Commented Apr 15, 2019 at 13:58
-
Expected result : [1.03041108,1.09852477,1.08630397]OwenS– OwenS2019-04-15 13:59:07 +00:00Commented Apr 15, 2019 at 13:59
Add a comment
|
1 Answer
You need to unnest the array, truncate every element, then aggregate the elements back into an array:
select array_agg(trunc(n, 8) order by idx)
from unnest(array[1.030411088488374,1.0985247734044379,1.0863039797613594]::numeric[]) with ordinality as t(n,idx);
If you need to do that a log, consider creating a function:
create function trunc_elements(p_numbers numeric[], p_num_digits int)
returns numeric[]
as
$$
select array_agg(trunc(n, p_num_digits) order by idx)
from unnest(p_numbers) with ordinality as t(n,idx);
$$
language sql;
3 Comments
OwenS
Thanks for suggestion, WITH ordinality seems not to be recognized
@OwenS: then you are using an unsupported Postgres version. If you do not need to preserve the original order of the elements, you can just leave it out.