Arrays

In Relyt, you can define columns in tables as variable-length multidimensional arrays. Elements within an array can be of any built-in or user-defined base type, enum type, composite type, range type, or domain.

---

Declare array types

To gain a clearer understanding of array types, let's start by creating a table:

CREATE TABLE test (
  f0 int[],
  f1 int[3],
  f2 int[3][],
  f3 int ARRAY,
  f4 int ARRAY[3]
) USING pg_zdb;

INSERT INTO test VALUES ('{1}', '{1}', '{{1}}', '{1}', '{1}'), ('{2,3}', '{2,3}', '{{2,3}}', '{2,3}', '{2,3}');

As demonstrated in the example above, Relyt supports two methods to declare arrays:

• By appending square brackets ([]) after the data type name of the array elements, such as int[] in the example.

• By following the data type name with the ARRAY keyword, as shown with int ARRAY in the example.

Usage notes

When declaring arrays, note the following guidelines:

• When you declare an array using the ARRAY keyword followed by brackets ([]), the brackets must contain a specific number.

• Array types declared with the ARRAY keyword are limited to one-dimensional arrays.

• The number within the square brackets ([]) indicates the length of the array in that dimension, although this number does not affect the actual length of data written.

In multidimensional arrays, each dimension of an element within the same record must have the same length, although elements in the same dimension may have different lengths across different records.

Relyt supports arrays up to 6 dimensions. Specifying a dimension greater than 6 during table creation will not trigger an error, but attempting to insert data later will. Here is an example:

-- The dimension of f0 is 7. The table can be successfully created.
CREATE TABLE test (
  f0 int[][][][][][][] 
) USING pg_zdb;

-- An error is reported when data is inserted to the table.
INSERT INTO test VALUES ('{{{{{{{1}}}}}}}');
ERROR:  number of array dimensions (7) exceeds the maximum allowed (6)
LINE 1: INSERT INTO test VALUES ('{{{{{{{1}}}}}}}');

Differences from PostgreSQL

In PostgreSQL, the displayed array length is for display only and does not restrict the length of data inserted. However, Relyt requires all rows in the same column to have dimensions consistent with those defined in the schema. For example:

CREATE TABLE test (
  f0 int[],       -- Dimension 1
  f1 int[][][],   -- Dimension 3
  f2 int ARRAY,   -- Dimension 1
  f3 int ARRAY[3] -- Dimension 1
) USING pg_zdb;

-- Dimensions match, and data is successfully inserted.
INSERT INTO test VALUES ('{1}', '{{{1}}}', '{1}', '{1}');

-- Dimensions do not match, and data fails to be inserted.
INSERT INTO test VALUES ('{{1}}', '{{1}}', '{{1}}', '{{1}}');

---

Array value input

When writing an array value as a literal constant, enclose element values in curly braces and separate them with commas. Enclose any element value containing commas or curly braces in double quotes. Here is the general format of an array constant:

'{<val1>, <val2>, ...}'

Each <val> can be either a constant or a subarray. Here is an example:

'{{1,2,3},{4,5,6},{7,8,9}}'

The example array is a two-dimensional, 3-by-3 array that consists of three integer subarrays.

To set an array element to NULL, write NULL at its position (any case variant of NULL is valid). To set the element to the string value NULL, enclose NULL in double quotes, making it "NULL".

In multidimensional arrays, each dimension of an element within the same record must have the same length, although elements in the same dimension may have different lengths across different records:

CREATE TABLE test (
  f0 int[],
  f1 int[3],
  f2 int[3][],
  f3 int ARRAY,
  f4 int ARRAY[3]
) USING pg_zdb;

INSERT INTO test VALUES (
  '{1,2,3}',             -- f0: One-dimensional array
  '{1,2,3}',             -- f1: One-dimensional array, size 3
  '{{1,2},{3,4,5}}',     -- f2: Two-dimensional array, inner array dimensions mismatch
  '{1,2,3,4}',           -- f3: One-dimensional array
  '{1,2,3}'              -- f4: One-dimensional array, size 3
);

ERROR:  multidimensional arrays must have array expressions with matching dimensions

When specifying NOT NULL for an array field, it only applies to the outermost dimension of the array, not the inner ones:

CREATE TABLE test (a int[][] not null);

INSERT INTO test VALUES (NULL);

ERROR:  null value in column "a" violates not-null constraint  (seg0 192.168.215.2:7005 pid=31718)
DETAIL:  Failing row contains (null).

-- Insert data again
INSERT INTO test VALUES ('{{0}, {NULL}}');
INSERT 0 1
SELECT * FROM test;


      a
--------------
 {{0},{NULL}}
(1 row)