PostgreSQL CBRT() Function

Summary: in this tutorial, you will learn how to use the PostgreSQL CBRT() function to calculate the cube root of a number.

Introduction to the PostgreSQL CBRT() function

A cube root number is a number that when you multiply itself twice, you’ll get the cube number. For example, 2 is a cube root number of 8 because when you multiply the number 2 by itself three times, you’ll get the number 8:

2 * 2 * 2 = 8Code language: SQL (Structured Query Language) (sql)

In PostgreSQL, the CBRT() is a math function that returns the cube root of a number.

Here’s the syntax of the CBRT() function:

CBRT(n)Code language: SQL (Structured Query Language) (sql)

In this syntax:

• n is the number that you want to calculate the cube root. n can be a literal number, an expression, or a table column.

The CBRT() function returns the cube root of a number n with the double precision type. If n is NULL, the CBRT() function returns NULL.

If n is a string, the CBRT() function will attempt to convert it to a number before calculating the cube root. If the conversion fails, it raises an error.

PostgreSQL CBRT() function examples

Let’s explore some examples of using the CBRT() function.

1) Basic PostgreSQL CBRT() function example

The following example uses the CBRT() function to calculate the cube root of 27:

SELECT CBRT(27) result;Code language: SQL (Structured Query Language) (sql)

Output:

 result
--------
      3Code language: SQL (Structured Query Language) (sql)

It returns 3 because 3* 3 *3 = 27.

2) Using CBRT() function with a negative number

The following example uses the CBRT() function to find the cube root of -27:

SELECT CBRT(-27) result;Code language: SQL (Structured Query Language) (sql)

Output:

 result
--------
     -3Code language: SQL (Structured Query Language) (sql)

The result is -3 because -3 * -3 * -3 is -27. Please note that the cube root of a negative number is always negative.

3) Using CBRT() function with numeric strings

The following example uses the CBRT() function with a numeric string:

SELECT CBRT('125') result;Code language: SQL (Structured Query Language) (sql)

Output:

 result
--------
      5Code language: SQL (Structured Query Language) (sql)

In this example, the CBRT() function converts the text ‘125’ to the number 125 and calculates the cube root.

4) Using the CBRT() function with table data

First, create a table called cube_volumes that stores the volumes of cubes:

CREATE TABLE cube_volumes(
    id INT GENERATED ALWAYS AS IDENTITY,
    volume DEC(19,2),
    PRIMARY KEY(id)
);Code language: SQL (Structured Query Language) (sql)

Second, insert rows into the cube_volumes table:

INSERT INTO
  cube_volumes (volume)
VALUES
  (8),
  (125),
  (NULL),
  (0) 
RETURNING *;Code language: SQL (Structured Query Language) (sql)

Output:

 id | volume
----+--------
  1 |   8.00
  2 | 125.00
  3 |   null
  4 |   0.00
(4 rows)Code language: SQL (Structured Query Language) (sql)

Third, calculate the side lengths of cubes using the CBRT() function:

SELECT
  id,
  volume,
  CBRT (volume) side_length
FROM
  cube_volumes;Code language: SQL (Structured Query Language) (sql)

Output:

 id | volume | side_length
----+--------+-------------
  1 |   8.00 |           2
  2 | 125.00 |           5
  3 |   null |        null
  4 |   0.00 |           0
(4 rows)Code language: SQL (Structured Query Language) (sql)

Summary

• Use the CBRT() function to calculate the cube root of a number.