Is there a function that adds together all numeric columns in a row?

Question

Using Postgres 15, how to get a sum of columns in each row from a table ?

-86750 | 1728 | 1500 | 61.69 | 415 |      130.18 |   303.99 | 288.57 |        93.44 |         78.06 |              0 |    561.69 |              257.49
-86100 |  798 |  700 |    68 | 415 |         130 |      303 |    288 |          128 |            20 |              0 |       561 |                 257

In particular, I want to add together values of columns that have the int or float data types, instead of referencing their names.

I mean not using something like:

for each row ....
     select toto+tata+titi+tutut from .... where rawid=....

But something more like:

for each row 
     if column is type float or int 
        variable+= it 
     fi

Is this possible?

score 1 · Accepted Answer · answered May 30 '23 at 14:23

You could use the JSONB functions. Note that this cannot differentiate between different types of numbers, only between numbers and strings.

SELECT
  (
    SELECT SUM(j::float)
    FROM to_jsonb(t) r
    CROSS JOIN LATERAL jsonb_path_query(r, '$.* ? (@.type() == "number")') j
  )
FROM YourTable t

db<>fiddle

This does the following, all inside a subquery:

First convert the whole row into JSONB
Pull out each property value, where the property type is number, as separate rows
Sum the data

The efficiency of this is going to be much worse than just naming each column explicitly.

Erwin Brandstetter · Answer 2 · 2023-05-31T11:57:21.693

You can have your pudding and eat it, too. Meaning, you can have the result just naming the table, and still get optimal performance, building the command dynamically.

This builds your command:

SELECT format('SELECT %1$s AS my_sum FROM %2$s'
            , string_agg(quote_ident(a.attname), ' + ')
            , min(attrelid)::regclass
             )
FROM   pg_catalog.pg_attribute a
JOIN   pg_catalog.pg_type t ON t.oid = a.atttypid
WHERE  a.attrelid = 'my_schema.my_table'::regclass  -- provide table name here, optionally schema-qualified!
AND    a.attnum > 0                 -- only user columns
AND    NOT a.attisdropped           -- no dropped columns
AND    t.typcategory = 'N';         -- all "numeric" types

Notably safe against SQL injection. See:

Safely quoting type names to protect against SQL-injection

Here I use the psql meta-command \gexec to immediately execute the built command:

test=> SELECT format('SELECT %1$s AS result FROM %2$s'
test(>             , string_agg(quote_ident(a.attname), ' + ')
test(>             , min(a.attrelid)::regclass
test(>              )
test-> FROM   pg_catalog.pg_attribute a
test-> JOIN   pg_catalog.pg_type t ON t.oid = a.atttypid
test-> WHERE  a.attrelid = 'my_table'::regclass
test-> AND    a.attnum > 0
test-> AND    NOT a.attisdropped
test-> AND    t.typcategory = 'N'\gexec
 result 
--------
     21
     23
 12

(4 rows)

See:

My function returned a string. How to execute it?

Alternatively, create a function that executes dynamic SQL.

You may want to wrap columns that can be null into COALESCE to not nullify the sum (like demonstrated in row 3 of above example). And you may want to cast to float8 (which can hold the sum of any numeric types) to get a consistent result type:

SELECT format('SELECT (%1$s)::float8 AS my_sum FROM %2$s'
            , string_agg(CASE WHEN a.attnotnull THEN quote_ident(a.attname) ELSE 'COALESCE(' || quote_ident(a.attname) || $$, '0')$$ END, ' + ')
            , min(a.attrelid)::regclass
             )
FROM   pg_catalog.pg_attribute a
JOIN   pg_catalog.pg_type t ON t.oid = a.atttypid
WHERE  a.attrelid = 'my_table'::regclass  -- provide table name here!
AND    a.attnum > 0
AND    NOT a.attisdropped
AND    t.typcategory = 'N'\gexec

The manual about pg_attribute and pg_type.

Is there a function that adds together all numeric columns in a row?

2 Answers2