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
:
1 | 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:
1 | 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
):
1 | 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:
1 | 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:
1 2 3 4 5 6 | SELECT SUM (int_col) AS col_total FROM some_table WHERE 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 ABS
function:
1 | SELECT ABS(-18.5); |
This example will return a response that looks like the following:
1 2 3 4 5 | 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:
1 2 3 4 5 | 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:
1 2 3 4 5 | 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:
1 2 3 4 5 | 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:
1 2 3 4 5 | 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:
1 2 3 4 5 | 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:
1 2 3 4 5 | 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:
1 2 3 4 5 | 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:
1 2 3 4 5 | 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:
1 2 3 4 5 | 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:
1 2 3 4 5 | 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.
1 2 3 4 5 | postgres=# SELECT POWER(5, 3); POWER ------- 125 (1 ROW) |
The PostgreSQL ‘RADIANS’ function
The RADIANS
function converts a value from degrees to radians:
1 2 3 4 5 | 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:
1 2 3 4 5 | 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:
1 2 3 4 5 | 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:
1 2 3 4 5 | 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:
1 2 3 4 5 | 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:
1 2 3 4 5 | 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