w3resource logo
gallery w3resource

PostgreSQL - Mathematical Functions slides presentation

 

This slide presentation describes PostgreSQL Mathematical functions with syntax and examples. Covering all the mathematical functions in this slides presentation.

Transcript

PostgreSQL Mathematical Functions

PostgreSQL abs() function

The PostgreSQL abs() returns the absolute value of a number passed as argument.

Example: SELECT ABS(-115.36) AS "Absolute Value", ABS(115.36) AS "Absolute Value";

Output:

      Absolute Value | Absolute Value
---------------------+---------------------
           115.36    |   115.36

PostgreSQL cbrt() function

The PostgreSQL cbrt() returns the cube root of a given number.

Example: SELECT CBRT(343) AS "Cube Root", CBRT(-343) AS "Cube Root";

Output:

      Cube Root |     Cube Root
----------------+----------------------
              7 |          -7

PostgreSQL ceil() function

The PostgreSQL ceil() will rounded up any positive or negative decimal value and return the value as greater than the argument.

Example: SELECT CEIL(-53.7) AS "Ceil", CEIL(53.7) AS "Ceil";

Output:

  Ceil | Ceil
-------+------
   -53 | 54

PostgreSQL ceiling() function

The PostgreSQL ceiling() will rounded up any positive or negative decimal value and return the value as greater than the argument.

Example: SELECT CEILING(-69.8) AS "Ceiling", CEILING(69.4) AS "Ceiling";

Output:

   Ceiling | Ceiling
-----------+---------
       -69 |      70

PostgreSQL degrees() function

The PostgreSQL degrees() will return the values in degrees from radian as specified in the argument.

Example: SELECT DEGREES(.45) AS "Degrees";

Output:

       Degrees
---------------------------
     25.783100780887

PostgreSQL div() function

The PostgreSQL div() will return the integer quotient of a division as specified in the argument.

Example: SELECT DIV(19,3) AS "Quotient", DIV(-27,5) AS "Quotient";

Output:

    Quotient | Quotient
-------------+-------------
           6 |       -5

PostgreSQL exp() function

The PostgreSQL exp() will return the exponentiation of a number as specified in the argument.

Example: SELECT EXP(2.0) AS "Exponential";

Output:

    Exponential
---------------------------------
    7.3890560989306502

PostgreSQL floor() function

The PostgreSQL floor() will rounded up any positive or negative decimal value and return the value as smaller than the argument.

Example: SELECT FLOOR(-53.6) AS "Floor",FLOOR(53.6) AS "Floor";

Output:

  Floor | Floor
--------+----------
    -54 | 53

PostgreSQL ln() function

The PostgreSQL ln() will return the natural logarithm of a given number, as specified in the argument.

Example: SELECT LN(3.0) AS "Natural Logarithm"

Output:

       Natural Logarithm
-------------------------------
      1.0986122886681097

PostgreSQL log() function

The PostgreSQL log() will return the base 10 logarithm of a given number or logarithm of a number for a particular base, as specified in the argument.

Example: SELECT LOG(200.0) AS "Base 10 Logarithm",LOG(2.0,16)

AS "Base 2 Logarithm";

Output:

            Base 10 Logarithm | Base 2 Logarithm
------------------------------+----------------------------
           2.3010299956639812 | 4.0000000000000000

PostgreSQL mod() function

The PostgreSQL mod() will return the remainder of a division of two numbers, as specified in the argument.

Example: SELECT MOD(38,5) AS "Remainder",MOD(-38,5) AS "Remainder";

Output:

       Remainder | Remainder
-----------------+----------------
               3 | -3

PostgreSQL pi() function

The PostgreSQL pi() will return the constant value of pi.

Example: SELECT PI() AS "Value of PI";

Output:

      Value of PI
-------------------------
    3.14159265358979

PostgreSQL power() function

The PostgreSQL power() will return the value of one number raised to the power of another number, provided in the argument.

Example: SELECT POWER(7.0,3) AS "7 raised to the power of 3",POWER(7,3) AS "7 raised to the power of 3";

Output:

            7 raised to the power of 3 | 7 raised to the power of 3
---------------------------------------+----------------------------------
                  343.0000000000000000 |                 343

PostgreSQL radians() function

The PostgreSQL radians() will return the value in radian from degrees, provided in the argument.

Example: SELECT RADIANS(15.0) AS "Degrees to Radians";

Output:

     Degrees to Radians
-------------------------------
     0.261799387799149

PostgreSQL random() function

The PostgreSQL random() will return the random value between 0 and 1.

Example: SELECT RANDOM() AS "Random Numbers";

Output:

      Random Numbers
------------------------------
      0.940978900529444

PostgreSQL round() function

The PostgreSQL round() will return the value after rounded a number upto a specific decimal places, provided in the argument.

Example: SELECT ROUND(67.456) AS "Round",ROUND(67.456,1) AS "Round upto 1 decimal",ROUND(67.456,2) AS "Round upto 2 decimal";

Output:

    Round |    Round upto 1 decimal       | Round upto 2 decimal
----------+-------------------------------+-------------------------------
      67  |              67.5             |           67.46

PostgreSQL sign() function

The PostgreSQL sign() will return the sign of a given number. It returns 1 if the number is positive and -1 if negative.

Example: SELECT SIGN(14.321) AS "Positive Number",SIGN(-14.321) AS "Negative Number";

Output:

       Positive Number | Negative Number
-----------------------+------------------------
                1      |         -1

PostgreSQL sqrt() function

The PostgreSQL sqrt() will return the square root of a given positive number.

Example: SELECT SQRT(225) AS "Square Root";

Output:

    Square Root
------------------
         15

PostgreSQL trunc() function

The PostgreSQL trunc() will truncate a number to a particular decimal places. If no decimal places is provided it truncate toward zero(0).

Example: SELECT TRUNC(67.456) AS "Truncate", TRUNC(67.456,1) AS "Truncate upto 1 decimal", TRUNC(67.456,2) AS "Truncate upto 2 decimal";

Output:

    Truncate |         Truncate upto 1 decimal |     Truncate upto 2 decimal
-------------+---------------------------------+--------------------------------
        67   |                    67.4         |               67.45