How to Do a Mathematical Operation within Pivotal Greenplum

Products

• VMware Tanzu Greenplum

Issue/Introduction

Pivotal Greenplum (aka Postgres) has a powerful utility to do simple math problem within the database. Below examples will show how this can be done.

 

Environment

Resolution

Have a look at the page here for the list of operations available. Let's take a list of few operations as an example to help you understand the powerful feature.

• Addition:

  gpadmin=# SELECT 1+2 AS result;
   result 
  --------
        3
  (1 row)

• Multiplication:

  gpadmin=# SELECT 1*2 AS result;
   result 
  --------
        2
  (1 row)

• Division:

  gpadmin=# select 20/7 as result;
   result 
  --------
        2
  (1 row)

• Division (with Decimals):

  gpadmin=# select 20/7::float as result;
        result      
  ------------------
   2.85714285714286
  (1 row)
  
  - OR -
  
  gpadmin=# select 20.0/7 as result;
         result       
  --------------------
   2.8571428571428571
  (1 row)
  
  Time: 0.976 ms

• Fraction:

   Time: 0.500 ms
  gpadmin=# SELECT 3! AS result;
   result 
  --------
        6
  (1 row)

• Square root:

  gpadmin=# SELECT |/64 AS result;
   result 
  --------
        8
  (1 row)

• pi

  gpadmin=# SELECT pi() AS result;
        result      
  ------------------
   3.14159265358979
  (1 row)

• tan(x)

  gpadmin=# SELECT tan(45) AS result;
        result      
  ------------------
   1.61977519054386
  (1 row)

Additional Information

+ Environment:

Pivotal Greenplum Database (GPDB) all versions