Doltgres, the world’s first version-controlled Postgres-compatible database, just hit 1.0, meaning that it’s ready for production use. We want Doltgres to be a drop-in replacement for Postgres so that customers can use the entire ecosystem of Postgres-compatible tools and libraries, or port their existing database applications to Doltgres without changing any code. This means getting all the nuanced semantics of Postgres’s behavior correct in our emulation. And we think we’ve done pretty well here — our compatibility tests encompass over two dozen tools and languages.

Today’s blog announces one small step towards our goal of 100% compatibility: support for multi-dimensional array values. All of the examples below now work as expected in the most recent release of Doltgres.

What are multi-dimensional arrays?#

Postgres supports array types of any other type, as you probably already know. So you can store an int in a column, or you can store an int[], which is an array of that same type. But you might not know that these array values can be 2-dimensional, 3-dimensional, or really any dimension you want. Postgres doesn’t constrain you here.

Here’s a table with a couple of array columns and some values.

CREATE TEMP TABLE array_examples (id text PRIMARY KEY, nums integer[], labels varchar[]);
INSERT INTO array_examples VALUES
 ('vector', ARRAY[1,2,3], ARRAY['red','blue']::varchar[]),
 ('matrix', ARRAY[[1,2,3],[4,5,6]], ARRAY[['red','blue'],['green','gold']]::varchar[]),
 ('cube', ARRAY[[[1,2],[3,4]],[[5,6],[7,8]]], NULL),
 ('wide', ARRAY[[10,20,30,40]], ARRAY[]::varchar[]),
 ('empty', ARRAY[]::integer[], ARRAY[]::varchar[]),
 ('null', NULL, NULL),
 ('null_element', ARRAY[1,NULL,3], ARRAY['NULL',NULL,'']::varchar[]);

Note the syntax for constructing array literal values. A one-dimensional array is easy:

ARRAY[1,2,3]

Making it two-dimensional requires another level of bracketing:

ARRAY[[1,2,3],[4,5,6]]

Some people prefer the string-literal method of input, where you use {} in a string value and then cast it to the array type of your choice. So this works too:

'{{1,2,3},{4,5,6}}'::int[]

You can access any element in a multi-dimensional array using repeated subscripts, with syntax that should be familiar to any programmer.

UPDATE array_examples SET nums[2][3]=60 WHERE id='matrix' RETURNING nums;

        nums        
--------------------
 {{1,2,3},{4,5,60}}
(1 row)

UPDATE 1

You can ask Postgres how many dimensions an array has with the array_ndims function.

SELECT id, labels, array_ndims(labels) AS dimensions FROM array_examples WHERE id IN ('vector','matrix') ORDER BY id;

   id   |          labels           | dimensions 
--------+---------------------------+------------
 matrix | {{red,blue},{green,gold}} |          2
 vector | {red,blue}                |          1
(2 rows)

Note that the same column, declared as varchar[], can hold both 1-dimensional and 2-dimensional arrays. And Postgres considers arrays with the same element type to have the same type, regardless of the number of dimensions. It doesn’t differentiate array types internally by dimension.

CREATE TEMP TABLE declared_shape (a integer[2][3]);
INSERT INTO declared_shape VALUES (ARRAY[1,2,3,4]), (ARRAY[[[9]]]);
SELECT a, pg_typeof(a), array_dims(a) FROM declared_shape;
SELECT 'integer[]'::regtype = 'integer[][]'::regtype AS same_type;

     a     | pg_typeof |   array_dims    
-----------+-----------+-----------------
 {1,2,3,4} | integer[] | [1:4]
 {{{9}}}   | integer[] | [1:1][1:1][1:1]
(2 rows)

 same_type 
-----------
 t
(1 row)

So are there any constraints on the dimensions of an array value? Just one: sub-arrays in each dimension must have the same length. So these statements are illegal:

SELECT ARRAY[[1,2],[3]]; -- ERROR:  2202E: multidimensional arrays must have array expressions with matching dimensions
SELECT ARRAY[ARRAY[1,2],ARRAY[[3,4],[5,6]]]; -- ERROR:  2202E: multidimensional arrays must have array expressions with matching dimensions

And while NULL elements are allowed in array types, a NULL value can’t stand in for a complete sub-array.

SELECT ARRAY[ARRAY[1,2],NULL::int[]]; -- ERROR:  2202E: multidimensional arrays must have array expressions with matching dimensions

Functions and operators on multi-dimensional arrays#

In typical fashion, Postgres defines a dizzying variety of functions and operators that work on arrays. There are too many to cover them all here, but they should all work. Here are a few of the more commonly used ones.

You can find the index of an element in an array with the array_position function. Remember that Postgres array indexes are 1-based. To find all occurrences of a repeated element, use array_positions, which returns an array of indexes.

SELECT array_position(ARRAY[10,20,10],10), array_positions(ARRAY[10,20,10],10);

 array_position | array_positions 
----------------+-----------------
              1 | {1,3}           
(1 row)

You can replace elements with the array_replace function. The dimensionality of the array doesn’t matter for this function — it works the same on arrays of any dimensionality.

SELECT array_replace(ARRAY[[1,NULL],[1,4]],1,9) AS replaced, array_replace(ARRAY[[1,NULL],[1,4]],NULL,0) AS fill_nulls;

     replaced     |  fill_nulls   
------------------+---------------
 {{9,NULL},{9,4}} | {{1,0},{1,4}}
(1 row)

You can concatenate two arrays with the || operator (as with strings), or use the array_cat function. Note that concatenation works slightly differently than you might expect for multi-dimensional arrays, treating array values with fewer dimensions as elements to be inserted into the array with more dimensions.

SELECT array_cat(ARRAY[[1,2],[3,4]],ARRAY[[5,6]]) AS add_rows, 
ARRAY[[1,2],[3,4]] || ARRAY[5,6] AS append_row, 
ARRAY[0,0] || ARRAY[[1,2],[3,4]] AS prepend_row;

      add_rows       |     append_row      |     prepend_row     
---------------------+---------------------+---------------------
 {{1,2},{3,4},{5,6}} | {{1,2},{3,4},{5,6}} | {{0,0},{1,2},{3,4}}
(1 row)

Most of these behave as you would expect with arrays of higher dimensionality, but there are some surprises. For example, the unnest function, which turns arrays into rows, implicitly flattens any multi-dimensional array rather than returning sub-array elements, as you might expect.

SELECT * FROM unnest(ARRAY[[1,2],[3,4]], ARRAY['a','b']::varchar[]) AS u(n,label);

 n | label  
---+--------
 1 | a
 2 | b
 3 | (NULL)
 4 | (NULL)
(4 rows)

And as always, unnest and other set-returning functions do some weird things when you combine them in non-standard ways, as we’ve talked about before.

There are many, many more of these, and they all now behave the same in Doltgres as in Postgres.

Conclusion#

Doltgres 1.0 already launched, but Doltgres’s compatibility story is definitely not over. Keep the issues coming and we’ll keep knocking them down in 24 hours.

Have a divergence from Postgres behavior to report? Want to learn more about Doltgres? Visit us on the DoltHub Discord, where our engineering team hangs out all day. Hope to see you there.