PostgreSQL - Mathematical Functions 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