PostgreSQL Math Functions
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
This command will attempt to connect to the database named
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
/ math operators.
Let’s look at a SQL example that returns all the records from the table
some_table where the value of
The PostgreSQL division function
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
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:
SUM (int_col) AS col_total
int_col >= 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
This example will return a response that looks like the following:
The PostgreSQL ‘CBRT’ function
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
Let’s look at a simple example:
The PostgreSQL ‘DEGREES’ function
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
We can see how this function works in the example below:
The PostgreSQL ‘LN’ function
LN function returns the natural logarithm of the given number:
The PostgreSQL ‘LOG’ function
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
The PostgreSQL ‘PI’ function
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
RADIANS function converts a value from degrees to radians:
The PostgreSQL ‘RANDOM’ function
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
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
TRUNC function allows users to specify a decimal value that will be truncated to a whole number.
Here’s an example:
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