0

If I want to add up two values in a query, I can use the + operator. If I want to sum up all values returned by a query, I can use SUM.

If I want to multiply two values in a query, I can use the * operator. If, however, I want to multiply together all values returned by a query, there does not appear to be a MULT or PRODUCT or the like.

I find myself using exp(sum(ln(X))), which is ridiculous. It's astonishing that this feature is not present since even more "esoteric" things like covariances and regression coefficients are conveniently available in some dialects.

Why is it that the SQL standard does not have this (I believe) "basic" feature? Surely it must be a conscious design decision. Are there any technical advantages to not having this operation or discouraging users from performing multiplication on large query results?

  • Why is opinion based, and will probably be closed as off-topic. For a solution, see for example stackoverflow.com/questions/13156055/… and stackoverflow.com/questions/21623282/… – Colin 't Hart May 15 at 14:23
  • Because it's not part of the SQL standard, may be? – mustaccio May 15 at 14:33
  • @Colin'tHart, don't think that design decisions such as this are opinion-based; there are technical reasons for and against implementing a specific feature. What I am looking for is technical reasons why a product function was not implemented in PostgreSQL by the developers. – Constantin May 15 at 14:49
  • @mustaccio, in that case, the question should be why it is not part of the SQL standard, will change it accordingly. – Constantin May 15 at 14:49
  • Just as an aside, the exp(sum(ln(x))) only works if X is always positive. You probably will need to change it to case when sum(case when X < 0 then 1 end)%2=0 then -1 else 1 end*exp(sum(ln(abs(X)))) for safety purposes. – Michael B May 15 at 16:56
1

Yes, the PostgreSQL core team has spent days deciding that this should not be included in core.

Seriously:

  • a mul operator is not in the SQL standard.

  • With PostgreSQL's extensibility, it is trivial to define such an aggregate function yourself:

    CREATE AGGREGATE mul(integer) (
       SFUNC = int4mul,
       STYPE = integer,
       INITCOND = '1'
    );
    

Not the answer you're looking for? Browse other questions tagged or ask your own question.