PostgreSQL has a lot of extensions that add features to the database. One of these extensions is called the cube extension. We can use this extension to represent points and areas in one or more dimensions. The cube extension has been a part of PostgreSQL for a time.
The cube extension is really useful when we have a lot of data that we need to store and compare. For example, we can use it for feature vectors, coordinate data, similarity search, geometric calculations, scientific measurements, and machine learning embeddings.
Check that the cube extension is available in your PostgreSQL like this.
select * from pg_available_extensions where name = 'cube';
Result:
name | default_version | installed_version | location | comment
------+-----------------+-------------------+----------+--------------------------------------
cube | 1.5 | | $system | data type for multidimensional cubes
(1 row)
Now create the extension.
create extension cube ;
Check the installed version of the created extension.
select * from pg_available_extensions where name = 'cube';
Result:
name | default_version | installed_version | location | comment
------+-----------------+-------------------+----------+--------------------------------------
cube | 1.5 | 1.5 | $system | data type for multidimensional cubes
(1 row)
Check the functionalities of the cube extension by using the command below.
\dx+ cube
Result:
Objects in extension "cube"
Object description
-----------------------------------------------------------------
function cube_cmp(cube,cube)
function cube_contained(cube,cube)
function cube_contains(cube,cube)
function cube_coord(cube,integer)
function cube_coord_llur(cube,integer)
function cube(cube,double precision)
function cube(cube,double precision,double precision)
function cube_dim(cube)
function cube_distance(cube,cube)
function cube(double precision)
function cube(double precision[])
function cube(double precision,double precision)
function cube(double precision[],double precision[])
function cube_enlarge(cube,double precision,integer)
function cube_eq(cube,cube)
function cube_ge(cube,cube)
function cube_gt(cube,cube)
function cube_in(cstring)
function cube_inter(cube,cube)
function cube_is_point(cube)
function cube_le(cube,cube)
function cube_ll_coord(cube,integer)
function cube_lt(cube,cube)
function cube_ne(cube,cube)
function cube_out(cube)
function cube_overlap(cube,cube)
function cube_recv(internal)
function cube_send(cube)
function cube_size(cube)
function cube_subset(cube,integer[])
function cube_union(cube,cube)
function cube_ur_coord(cube,integer)
function distance_chebyshev(cube,cube)
function distance_taxicab(cube,cube)
function g_cube_consistent(internal,cube,smallint,oid,internal)
function g_cube_distance(internal,cube,smallint,oid,internal)
function g_cube_penalty(internal,internal,internal)
function g_cube_picksplit(internal,internal)
function g_cube_same(cube,cube,internal)
function g_cube_union(internal,internal)
We will explore the most important functionalities of the cube extension. So let's create a sample table and insert values using cube().
CREATE TABLE products ( id serial PRIMARY KEY, name text, embedding cube);INSERT INTO products (name, embedding)VALUES('Laptop', cube(ARRAY[0.9, 0.2, 0.7])),('Phone', cube(ARRAY[0.8, 0.1, 0.6])),('Tablet', cube(ARRAY[0.4, 0.7, 0.3])),('Watch', cube(ARRAY[0.2, 0.9, 0.8]));Check the records from the products table.
select * from products;
Result:
id | name | embedding
----+--------+-----------------
1 | Laptop | (0.9, 0.2, 0.7)
2 | Phone | (0.8, 0.1, 0.6)
3 | Tablet | (0.4, 0.7, 0.3)
4 | Watch | (0.2, 0.9, 0.8)
(4 rows)
Here we can see that the values of the embeddings column are enclosed within “()”.
In PostgreSQL, we can simply use the ARRAY[] to display a set of numbers in array format like this.
SELECT ARRAY[1,2,3];
Result:
array
---------
{1,2,3}
(1 row)
We can see that the results are enclosed within curly brackets.
1. cube()
Now use the cube() with the ARRAY[] like this.
SELECT cube(ARRAY[1,2,3]);
Result:
cube
-----------
(1, 2, 3)
(1 row)
Now the result is enclosed within parentheses when we use cube() with array[].
2. cube_dim()
SELECT cube_dim(cube(ARRAY[10,20,30,40]));
Result:
cube_dim
----------
4
(1 row)
The main purpose of this cube_dim() function is to return the dimensions of the cube.
Here 10 - first dimension
20 - second dimension
30 - third dimension
40 - fourth dimension
We can also use the cube() inside cube_dim() like this.
SELECT cube_dim(cube(ARRAY[10,20,30,40,50]));
Result:
cube_dim
----------
5
(1 row)
3. cube_is_point()
SELECT cube_is_point(cube(ARRAY[1,2,3]));
Result:
cube_is_point
---------------
t
(1 row)
The function cube_is_point() checks if a cube object is a single point or a multidimensional box.
It returns true (t) when the cube is a point, which means the left corner and the upper-right corner are exactly the same.
It returns false (f) when the cube is a box that has volume meaning the left corner and the upper-right corner are different.
SELECT cube_is_point(
cube(ARRAY[1,2,3], ARRAY[4,5,6])
);
Result:
cube_is_point
---------------
f
(1 row)
4. cube_size()
SELECT cube_size(cube(ARRAY[1,1], ARRAY[4,5]));
Result:
cube_size
-----------
12
(1 row)
The cube_size function can be used to figure out the volume of a cube.
If the cube is -
In one dimension, the cube_size function gives you the length.
In two dimensions, the cube_size function gives you the area of the cube.
In three dimensions, the cube_size function returns the volume of the cube.
For cubes with higher dimensions, the cube_size function calculates the hypervolume, which is the product of all the lengths in each dimension of the cube.
5. cube_distance()
SELECT cube_distance(
cube(ARRAY[1,2]),
cube(ARRAY[4,6])
);
Result:
cube_distance
---------------
5
(1 row)
The cube_distance() can be used to get the distance between two cubes.
The calculation of distance works like this.
Step 1: Find the difference along each axis
X-coordinate difference
4 - 1 = 3
Y-coordinate difference
6 - 2 = 4
Step 2: Square each difference
3² = 9
4² = 16
Step 3: Add the squared values
9 + 16 = 25
Step 4: Take the square root
v25 = 5
6. distance_taxicab()
SELECT distance_taxicab(
cube(ARRAY[1,2]),
cube(ARRAY[4,6])
);
Result:
distance_taxicab
------------------
7
(1 row)
This function named distance_taxicab() is used to return the taxicab distance between cubes.
Step 1: Find the difference along each axis
X-coordinate difference
|4 - 1| = 3
Y-coordinate difference
|6 - 2| = 4
Step 2: Add the absolute differences
3 + 4 = 7
7. distance_chebyshev()
SELECT distance_chebyshev(
cube(ARRAY[1,2]),
cube(ARRAY[4,6])
);
Result:
distance_chebyshev
--------------------
4
(1 row)
The distance_chebyshev() function calculates the Chebyshev distance between two cubes.
Step 1: Calculate the difference along each axis
X-coordinate difference
|4 - 1| = 3
Y-coordinate difference
|6 - 2| = 4
Step 2: Find the maximum difference
max(3, 4) = 4
8.cube_contains()
SELECT cube_contains(
cube(ARRAY[0,0], ARRAY[10,10]),
cube(ARRAY[2,2], ARRAY[5,5])
);
Result:
cube_contains
---------------
t
(1 row)
The cube_contains() function checks whether one cube completely contains another cube.
It returns true if the first cube fully contains the second cube.
It returns false otherwise.
SELECT cube_contains(
cube(ARRAY[0,0], ARRAY[10,10]),
cube(ARRAY[8,8], ARRAY[12,12])
);
Result:
cube_contains
---------------
f
(1 row)
9.cube_contained()
SELECT cube_contained(
cube(ARRAY[2,2], ARRAY[5,5]),
cube(ARRAY[0,0], ARRAY[10,10])
);
Result:
cube_contained
----------------
t
(1 row)
The cube_contained() function checks whether the first cube is completely contained within the second cube.
It returns true if the first cube lies entirely inside the second cube.
It returns false otherwise.
10.cube_overlap()
SELECT cube_overlap(
cube(ARRAY[0,0], ARRAY[5,5]),
cube(ARRAY[4,4], ARRAY[8,8])
);
Result:
cube_overlap
--------------
t
(1 row)
The cube_overlap() function checks whether two cubes overlap (intersect).
It returns true if the cubes share any common area or volume, even if it's very small.
It returns false if the cubes are completely separate.
11.cube_union()
SELECT cube_union(
cube(ARRAY[1,1], ARRAY[3,3]),
cube(ARRAY[2,2], ARRAY[5,6])
);
Result:
cube_union
---------------
(1, 1),(5, 6)
(1 row)
The cube_union function gives us the cube that can fit around both of the input cubes.
It does not change the shape of the cubes into something. The cube_union function makes a cube. This new cube has a corner with the smallest point from each side. The upper corner of this cube has the biggest point from each side.
The cube extension is a useful tool for PostgreSQL when you are working with multidimensional data. You can use it to store points and regions in an amount of space. The cube extension also has functions that can measure how far apart things are, compare objects, check if one thing is inside another, see if things overlap, and put cubes together to make bigger areas.
In this article, we talked about how to get the cube extension, make cube values, and use some of its functions with real examples. The cube extension is helpful when you are working with coordinate data, feature vectors, searching for things, or measuring things in many dimensions. It can make a lot of tasks easier that would normally require complicated math.
If your application needs to handle data in an efficient way, you should take a look at the cube extension. With a few SQL functions, you can do powerful things with geometry and distance inside the database.