0

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.

Community
  • 1
  • 1
Beeb
  • 53
  • 2
  • 8
  • 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 Jan 27 '15 at 06: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 Jan 27 '15 at 07:36
  • @Tommaso thanks again for looking this over! – Beeb Jan 27 '15 at 15:36

1 Answers1

2

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);
Tom-db
  • 6,528
  • 3
  • 30
  • 44