In Postgres, what is the best manner of aggregating the values of a “returning” clause

aggregateinsertpostgresqlupdate

I would like to query an aggregate of values that get updated. For a simple and contrived example, if this were legal syntax I would do :

update mytable set mynumber = mynumber + 1 returning sum(mynumber);

An aggregate function is not allowed in the returning clause. If this is possible, what is the best way to achieve this?

Best Answer

Use a CTE:

WITH numbers AS (
   update mytable set mynumber = mynumber + 1 returning mynumber
)
SELECT sum(mynumber) FROM numbers;

Related Solutions

Postgresql – Getting around the constraint “column must appear in the GROUP BY clause or be used in an aggregate function”

seharusnya seperti ini(supposed to be like this):

SELECT person.name, person.id, license.expiry_date, COUNT(car) FROM person
  JOIN license ON license.person_id = person.id
  JOIN car ON car.owner_id = person.id
WHERE person.name = 'Charles Bannerman'
GROUP BY person.name, person.id, license.expiry_date, car.car;

Ubuntu – How to get octal file permissions from command line

You can try

stat -c "%a %n" *

Replace * with the relevant directory or the exact filename that you want to examine.

From the man page of stat,

-c  --format=FORMAT
          use  the  specified  FORMAT instead of the default; output a newline after
          each use of FORMAT
%a     Access rights in octal
%n     File name

Usage:

With files:

$ stat -c "%a %n" ./Documents/Udev.html 
664 ./Documents/Udev.html

With folders:

$ stat -c "%a %n" ./Documents/
755 ./Documents/

(Reference)

Best Answer

Related Solutions

Postgresql – Getting around the constraint “column must appear in the GROUP BY clause or be used in an aggregate function”

Ubuntu – How to get octal file permissions from command line

Related Question