sum and length of postgresql integer array?

Question

I am trying to sum the contents of a postgresql array and also determine its length. In all cases, I am working with columns that are of integer array type.

ERROR:  function sum(integer[]) does not exist
LINE 1: select sum(interested) from deals;
               ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
db5=> select SUM(interested) from deals;
ERROR:  function sum(integer[]) does not exist
LINE 1: select SUM(interested) from deals;
               ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
db5=> select array_length(interested) from deals;
ERROR:  function array_length(integer[]) does not exist
LINE 1: select array_length(interested) from deals;
               ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
db5=>

Contrary to what I have read (Select sum of an array column in PostgreSQL, http://www.postgresql.org/docs/8.4/static/functions-array.html) sum(column) and array_length(column, 1) do not seem to perform as expected for me. Am I looking at the wrong docs? How can I get the sum and length of a postgre integer array?

Also, these are the php queries I am using along with pg_query to run these calls:

$query = "UPDATE deals set remaining = max  - array_length(interested, 1), until = min -array_length(interested, 1";
$query = "UPDATE deals set remaining = max - (SELECT SUM FROM UNNEST(hours)), until = min - (SELECT SUM FROM UNNEST(hours))";

I modified them to take Patrick's suggestions from the comments into account, but I still get a syntax error, e.g.

Warning: pg_query(): Query failed: ERROR: syntax error at end of input LINE 1: ...ngth(interested, 1), until = min -array_length(interested, 1 ^ in /var/www/html/join.php on line 162

Thank you.

For the array length you should indicate the dimension whose length you want to know, so: array_length(interested, 1). Summing arrays is not supported by default because of all the variations in array configuration and content. What you can do is do exactly what the other SO question had as an answer: unnest the array, then use the built-in SQL aggregate function SUM. Maybe not pretty, but it works. — Patrick
– Patrick, Commented Jan 27, 2015 at 6:15
You forgot a bracket at the end of the update statement: $query = "UPDATE deals set remaining = max - array_length(interested, 1), until = min -array_length(interested, 1)"; — Tom-db
– Tom-db, Commented Jan 27, 2015 at 7:36

Tom-db · Accepted Answer · 2015-01-27 08:12:09Z

1

In the update statement you just forgot a bracket at end of the statement (see comment).

In the second update statement, you cannot use sum in UPDATE, because aggregate functions are not allowed in update. Use a custom sql function which sums all elements in a array and use it in the update:

CREATE FUNCTION array_sum(NUMERIC[]) returns numeric AS 
$$
  SELECT sum(unnest) FROM (select unnest($1)) as foo;
$$
LANGUAGE sql;

The update statement:

UPDATE deals SET remaining = max -  array_sum(hours), until = min - array_sum(hours);

edited Jan 27, 2015 at 8:12

answered Jan 27, 2015 at 7:57

Tom-db

6,9584 gold badges33 silver badges51 bronze badges

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

1 Comment

Beeb Over a year ago

that did the trick. Many thanks. I didn't know sum doesn't work in UPDATE.

Collectives™ on Stack Overflow

sum and length of postgresql integer array?

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related