Add values from within a column with postgres

Asked

Viewed 2,762 times

2

I’m trying to sum the values from inside a column with postgres, the table structure is as follows:

id    |nome_municipio    |valores
1     |Porto Alegre      |100.01;95.0;50.1
2     |Ivoti             |87.0;80.1;45.1
3     |Novo Hamburgo     |210.0;99.2;100.0

I would like the end result to be

id    |nome_municipio    |valores   
1     |Porto Alegre      |245.2
2     |Ivoti             |207.2
3     |Novo Hamburgo     |409.2

That’s possible?

Postgres 9.1

sql postgresql

1 answer

3


select id , nome_municipio,  (SELECT SUM(s) FROM
UNNEST(CAST(regexp_split_to_array(valores, E';') as real[])) s)   from "tabela"

  • I was making an answer using regexp_split_to_table(). Wouldn’t it be easier to allow select sum(s) without the need for conversion?

  • It would be, but postgresql does not allow without making explicit conversion on the UNNEST part :(

Browser other questions tagged sql postgresql

You are not signed in. Login or sign up in order to post.