How to round each array element in an array column in Postgres?

Question

Say I have a table results that contains a score column that is an array full of scores:

CREATE TABLE results (
  id serial PRIMARY KEY,
  scores numeric[]
);

I would like to update the table so that I round each score to 4 decimal places.

I have created a rounding function round_numeric_array that works for a single array value:

CREATE OR REPLACE FUNCTION round_numeric_array (numeric[]) RETURNS numeric[]
LANGUAGE SQL
AS $$
   SELECT array_agg(round(unnest($1), 4))
$$;

But how do I apply it to every value in the table? I've been trying

UPDATE results SET scores = round_numeric_array(scores)

But I get a set-valued function called in context that cannot accept a set error. Any ideas?

klin · Accepted Answer · 2022-08-09 16:22:25Z

5

Place the unnest() function in the FROM clause:

CREATE OR REPLACE FUNCTION round_numeric_array (numeric[]) 
RETURNS numeric[]
IMMUTABLE
LANGUAGE SQL 
AS $$
   SELECT array_agg(round(elem, 4))
   FROM unnest($1) as arr(elem);
$$;

Note, that the function is immutable, read more in the documentation.

edited Aug 9, 2022 at 16:22

answered Oct 31, 2017 at 19:03

klin

123k15 gold badges240 silver badges262 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to round each array element in an array column in Postgres?

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related