Postgres 9.6

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

select version();


version


PostgreSQL 9.6.24 on x86_64-pc-linux-musl, compiled by gcc (Alpine 11.2.1_git20220219) 11.2.1 20220219, 64-bit


SELECT 1

create table test (number int, name varchar, result text);
insert into test values(1,'Kevin','{{2.0,10},{3.0,50}}');
insert into test values(2,'Max','{{2.0,10,3},{3.0,50,4},{3.0,50,4}}');
INSERT 0 1

alter table test add column average decimal;
ALTER TABLE

select * from test


number
name
result
average


1
Kevin
{{2.0,10},{3.0,50}}
null

2
Max
{{2.0,10,3},{3.0,50,4},{3.0,50,4}}
null


SELECT 2

update test t1
set average = t2.value_
from 
(
select
number, 
sum(t::decimal)/2 as value_ 
from test 
cross join lateral unnest((result::text[][])[1:999][1]) as t 
group by 1
) t2 
where t1.number=t2.number
UPDATE 2

select * from test


number
name
result
average


1
Kevin
{{2.0,10},{3.0,50}}
2.5000000000000000

2
Max
{{2.0,10,3},{3.0,50,4},{3.0,50,4}}
4.0000000000000000


SELECT 2

`number`	`name`	`result`	`average`
`1`	`Kevin`	`{{2.0,10},{3.0,50}}`	`null`
`2`	`Max`	`{{2.0,10,3},{3.0,50,4},{3.0,50,4}}`	`null`

`number`	`name`	`result`	`average`
`1`	`Kevin`	`{{2.0,10},{3.0,50}}`	`2.5000000000000000`
`2`	`Max`	`{{2.0,10,3},{3.0,50,4},{3.0,50,4}}`	`4.0000000000000000`