23694

Product Aggregate in PostgreSQL

Question:

I try to create an aggregate for product (*) in PostgreSQL. The field type of my row is "double precision"

So, I tried :

CREATE AGGREGATE nmul(numeric) ( sfunc = numeric_mul, stype = numeric );

When I launch my query, the result :

ERROR: function nmul(double precision) does not exist LINE 4: CAST(nmul("cote") AS INT),

Thank you

Answer1:

I found a solution from a very smart guy, who realized you can use logarithms to achieve this (<a href="https://www.postgresql.org/message-id/gtmhfi%24u26%242%40reversiblemaps.ath.cx" rel="nofollow">credit goes to him</a>):

select exp(sum(ln(x))) from generate_series(1,5) x; exp ----- 120 (1 row)

Answer2:

Cast your input from double precision (float8) to numeric, or define a double precision flavour of your aggregate.

Your aggregate works fine:

regress=> CREATE AGGREGATE nmul(numeric) regress-> ( regress(> sfunc = numeric_mul, regress(> stype = numeric regress(> ); regress=> SELECT nmul(x) FROM generate_series(1,100) x; nmul ---------------------------------------------------------------------------------------------------------------------------------------------------------------- 93326215443944152681699238856266700490715968264381621468592963895217599993229915608941463976156518286253697920827223758251185210916864000000000000000000000000 (1 row)

the issue is your query:

regress=> SELECT nmul(x::float8) FROM generate_series(1,100) x; ERROR: function nmul(double precision) does not exist LINE 1: SELECT nmul(x::float8) FROM generate_series(1,100) x; ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts.

You can define a float8 version of your aggregate (float8 is a synonym for double precision):

regress=> CREATE AGGREGATE nmul(double precision) ( sfunc = float8mul, stype = float8 ); regress=> SELECT nmul(x::float8) FROM generate_series(1,100) x; fmul ----------------------- 9.33262154439441e+157 (1 row)

or cast to numeric before aggregation if you want to retain the full precision of the value, eg:

CAST(nmul(CAST("cote" AS numeric)) AS INT)

or the PostgreSQL-specific shorthand cast:

nmul("cote"::numeric)::integer

Note that integer will overflow very quickly when you're working with these product aggregates:

regress=> SELECT nmul(x)::integer FROM generate_series(1,12) x; nmul ----------- 479001600 (1 row) regress=> SELECT nmul(x)::integer FROM generate_series(1,13) x; ERROR: integer out of range regress=>

so you're likely to want to stick with numeric anyway.

Recommend

  • What addresses are exactly, returned by MX lookup?
  • use generator as subprocess input; got “I/O operation on closed file” exception
  • Daylight saving issue while calculating Elapsed Time in C#
  • Error when updating to Gradle 3.3.2 in Android Studio: Cannot resolve symbol R
  • Tail recursive algorithm for generating all topological orderings in a graph
  • Facebook integration in iphone OS 4.0 or later
  • Javascript simple regex to find root domain
  • Java: Generating a random numbers with a logarithmic distribution
  • What's the idiomatic way to pass by mutable value?
  • Best_In_Place inline edits with nested attributes
  • Asynchronous execution aspect using AspectJ
  • AOP pointcut expression for any public method of a service
  • How can tfs work item details be exported?
  • Python: access structure field through its name in a string
  • Google Tag Manager delaying window.load?
  • Download CSV File by posting JSON data in ASP.NET MVC
  • How to retrieve user's newsfeed list (VK.com)?
  • How To Separate Strings For UIScrollView/UITextView based on the size of the frame
  • openTSDB REST API is not storing data
  • Use of x`apply` to speed up loops
  • build an API for streaming audio/video from GridFS mongodb
  • Magento Layered Navigation block. Move to center
  • Remove stopwords and tolower function slow on a Corpus in R
  • JQuery Mobile Ajax Navigation in Single-Page Template
  • git clone, upload-pack out of memory
  • How to add html image in to velocity template file to send email?
  • How to merge objects within array based on attribute
  • concise way of flattening multiindex columns
  • How do I add a mouse over tooltip to an Image using .DrawImage()
  • reshape/remould data frame to create normalized bar chart and pie chart
  • Google App Engine Datastore: Dealing with eventual consistency
  • ssh remote server login script
  • How to call different template for different category archive page in woocommerce
  • Accessing Arguments, Workflow Variables from custom activities
  • ReferenceError: TextEncoder is not defined
  • Create/delete users from text file using Bash script
  • Time Complexity of Fibonacci Algorithm [duplicate]
  • How to check if object is null in Java?