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:

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:

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):

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:

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:

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:

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

The PostgreSQL ‘CBRT’ function

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

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:

The PostgreSQL ‘DEGREES’ function

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

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:

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:

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:

The PostgreSQL ‘LN’ function

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

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:

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:

The PostgreSQL ‘PI’ function

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

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.

The PostgreSQL ‘RADIANS’ function

The RADIANS function converts a value from degrees to radians:

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:

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:

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:

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:

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:

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.