PostgreSQL Math Functions

Introduction

Many data types in PostgreSQL allow for math operations to be performed on them. There are a wide variety of PostgreSQL math functions available that enable users to perform mathematical operations quickly and efficiently. In this article, we’ll provide an overview of some of the most common PostgreSQL math functions and give some examples of their use.

Prerequisites to using PostgreSQL math functions

Before proceeding with this tutorial, you’ll need to have Postgres installed. You’ll also need to have a PostgreSQL role with access to a database before attempting the SQL examples found in this article.

We’ll be using the psql command-line interface to execute our SQL statements. You can use the psql -V command to have Postgres return the installed version of psql on your machine.

Accessing PostgreSQL using the ‘psql’ command line interface

The following syntax is used to access a PostgreSQL database on your local machine using psql:

psql postgres

This command will attempt to connect to the database named postgres.

You can also use the following syntax to connect to Postgres using a username, host, and database name:

psql -U some_username -h 127.0.0.1 -d some_database

NOTE: There are a few flags used in this syntax that are worth pointing out. The -U flag is for the Postgres username, the -h flag is for the host domain or IP address, and the -d flag is for the database name. If you only supply one parameter, psql will interpret it as the database name.

Use the SQL ‘SELECT’ statement to execute math functions in PostgreSQL

In this section, we’ll list the different kinds of math functions available to you when executing SQL commands to a PostgreSQL database.

Using PostgreSQL math operators while querying records

If you’d like to perform basic math operations within a SQL SELECT statement, you can use the +, *, -, and / math operators.

Let’s look at a SQL example that returns all the records from the table some_table where the value of int_col equals 123 (or 246/2):

SELECT * FROM some_table WHERE int_col=246/2;

The PostgreSQL division function

The DIV() function allows you to divide by passing two numerical values delimited by a comma. Here’s an example of how you would use the DIV() function in a SELECT statement:

SELECT DIV(246, 2);

The PostgreSQL ‘SUM’ function

If you’d like to add or combine numerical values from a Postgres table column, you can use the SUM() function. The following SELECT statement shows an example that will add up all values from a table’s int_col column where the record’s value is greater than or equal to 42:

SELECT
SUM (int_col) AS col_total
FROM
some_table
WHERE
int_col >= 42;

Screenshot of psql PostgreSQL math functions returning values and Postgres records

The PostgreSQL ‘ABS’ function

The name of the ABS function indicates that it returns the absolute value of a number; in other words, it will return the positive value of any number.

Here’s an example that uses the ABS function:

SELECT ABS(-18.5);

This example will return a response that looks like the following:

postgres=# SELECT ABS(-18.5);
abs
------
18.5
(1 ROW)

The PostgreSQL ‘CBRT’ function

The CBRT function returns the cube root of a number, as seen in the example below:

postgres=# SELECT CBRT(27);
cbrt
------
3
(1 ROW)

The PostgreSQL ‘CEIL’ function

If you want to round up to the nearest integer greater than or equal to a number, you can use the CEIL function.

Let’s look at a simple example:

postgres=# SELECT CEIL(45.7);
CEIL
------
46
(1 ROW)

The PostgreSQL ‘DEGREES’ function

The DEGREES function is used to convert radians into degrees, as seen in the example shown below:

postgres=# SELECT DEGREES(.45);
degrees
-----------------
25.783100780887
(1 ROW)

The PostgreSQL ‘DIV’ function

In a previous section, we introduced the DIV function, which returns the integer quotient of two numeric values.

We can see how it works in the following example:

postgres=# SELECT DIV(19,3);
div
-----
6
(1 ROW)

The PostgreSQL ‘EXP’ function

We can use the EXP function to obtain the exponential value of a number in scientific notation.

Here’s an example of the EXP function at work:

postgres=# SELECT EXP(2.0);
EXP
--------------------
7.3890560989306502
(1 ROW)

The PostgreSQL ‘FLOOR’ function

If you’d like to round down to the nearest integer less than or equal to a number, you can use the FLOOR function.

We can see how this function works in the example below:

postgres=# SELECT FLOOR(10.6);
FLOOR
-------
10
(1 ROW)

The PostgreSQL ‘LN’ function

Using the LN function returns the natural logarithm of the given number:

postgres=# SELECT LN(3);
ln
------------------
1.09861228866811
(1 ROW)

The PostgreSQL ‘LOG’ function

The LOG function returns a numeric value that represents the base 10 logarithm of a value.

An example of the LOG function in action is shown below:

postgres=# SELECT LOG(2, 64);
log
--------------------
6.0000000000000000
(1 ROW)

The PostgreSQL ‘MOD’ function

The name of the MOD function stands for “modulo”. This function divides two numbers and returns the remainder.

Let’s look at an example using the MOD function:

postgres=# SELECT MOD(10, 4);
MOD
-----
2
(1 ROW)

The PostgreSQL ‘PI’ function

The PI function simply returns the value of pi, as you can see in the following example:

postgres=# SELECT PI();
pi
------------------
3.14159265358979
(1 ROW)

The PostgreSQL ‘POWER’ function

You can use the POWER function to raise the power of the numeric value in the first argument to the power of the numeric value supplied in the second argument.

postgres=# SELECT POWER(5, 3);
POWER
-------
125
(1 ROW)

The PostgreSQL ‘RADIANS’ function

The RADIANS function converts a value from degrees to radians:

postgres=# SELECT RADIANS(60);
radians
-----------------
1.0471975511966
(1 ROW)

The PostgreSQL ‘RANDOM’ function

The RANDOM function will return a random number between 0 and 1. We can see an example of this function in action below:

postgres=# SELECT RANDOM();
random
-------------------
0.911168686114252
(1 ROW)

The Postgres ‘ROUND’ function

If you need to round a number to the nearest integer, you can use the ROUND function, as shown in the following example:

postgres=# SELECT ROUND(11.3);
round
-------
11
(1 ROW)

The PostgreSQL ‘SIGN’ function

The SIGN function simply returns the sign of a number: -1, 0 or +1.

An example of this function is shown below:

postgres=# SELECT SIGN(-1);
sign
------
-1
(1 ROW)

The PostgreSQL ‘SQRT’ function

You can use the SQRT function to return the square root of a numeric value, as seen in the simple example shown below:

postgres=# SELECT SQRT(49);
SQRT
------
7
(1 ROW)

The PostgreSQL ‘TRUNC’ function

The TRUNC function allows users to specify a decimal value that will be truncated to a whole number.

Here’s an example:

postgres=# SELECT TRUNC(12.3);
trunc
-------
12
(1 ROW)

Conclusion

When you’re working with numeric data in PostgreSQL, you may find yourself needing to perform mathematical operations on those values. PostgreSQL offers a wide variety of math functions that can be used to manipulate numeric values. In this article, we introduced you to the most common PostgreSQL math functions. With the examples provided in this tutorial to guide you, you’ll be able to implement these math functions in your own PostgreSQL queries.

Pilot the ObjectRocket Platform Free!

Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.

Get Started

Keep in the know!

Subscribe to our emails and we’ll let you know what’s going on at ObjectRocket. We hate spam and make it easy to unsubscribe.