SQL Array: Explained in Detail

Department | employee_names  ------------------------------------------------------  HR         | {Employee B, Employee E}  Finance    | {Employee A}  IT         | {Employee C, Employee D}

 

2) ARRAY_APPEND 

The ARRAY_APPEND function allows you to append values to an existing Array. Bear in mind that the elements you want to add should be of the same data type as the ones already existing in the Array. Here’s how you can add an element using the ARRAY_APPEND function:    

Disclaimer: Unfortunately, there’s no standard ARRAY_APPEND function in PostgreSQL. You’ll have to create a custom function named ARRAY_APPEND to get the job done. 
 

-- Create a custom function ARRAY_APPEND  CREATE OR REPLACE FUNCTION ARRAY_APPEND(arr anyarray, elem anyelem)  RETURNS anyarray AS  $$  BEGIN       RETURN arr || elem;  END;  $$  LANGUAGE plpgsql;

 

Now, you can use the ARRAY_APPEND function to append elements to an Array. Suppose you want to add the number “5” to an existing Array in a table of numbers. Here’s how you can do it: 
 

CREATE TABLE Numbers (  ID SERIAL PRIMARY KEY,  values INTEGER[]  );  INSERT INTO Numbers (values) VALUES (‘{1,2,3,4}’);

 

Now that the table and the Array have been defined, you can proceed with the addition of the number “5.” 
 

UPDATE Numbers  SET values = ARRAY_APPEND (values, 5)  WHERE ID = 1;

 

Once added, the updated Array will become ‘{1,2,3,4,5}.’ 

3) ARRAY_CONCAT 

As the name suggests, you can combine two or more Arrays to make a new one using this function. In PostgreSQL, you can perform concatenation using two different methods. One requires you to create a custom ‘ARRAY_CONCAT’ function, while the other can be done using the ‘array_cat’ function. Here’s how the first method works: 
 

-- Create a custom function ARRAY_CONCAT  CREATE OR REPLACE FUNCTION ARRAY_CONCAT (arr1 anyarray, arr2 anyarray)  RETURNS anyarray AS  $$  BEGIN  RETURN arr1 || arr2;  END;  $$  LANGUAGE plpgsql;

 

Now that your custom function has been created, you can use it to do the desired operation. Suppose you have two Arrays, namely {0,1,2} and {3,4,5}. Here’s how you can perform concatenation on the same: 
 

SELECT ARRAY_CONCAT (ARRAY[0,1,2], ARRAY[3,4,5]) AS concatenated_array;

 

The output will look something like this: 
 

concatenated_array  ---------------------------  {0,1,2,3,4,5}

 

Now, for the second method, here’s how you can get the same result: 
 

SELECT ARRAY[0,1,2] AS array1, ARRAY[3,4,5] AS array2;  Deploying ‘array_cat’ will look something like this:  SELECT array_cat(array1, array2) AS concatenated_array FROM (SELECT ARRAY[0,1,2] AS array1, ARRAY[3,4,5] AS array2) AS subquery;

 

The output for this method will look something like this: 
 

concatenated_array  ---------------------------  {0,1,2,3,4,5}

 

Learn to troubleshoot SQL problems with our SQL Server 2014 Performance Tuning and Optimisation 55144BC Training – Sign up now!

4) ARRAY_JOIN 

The ‘ARRAY_JOIN’ function allows you to concatenate elements from an Array consisting of strings. While the function itself isn’t a standard one, you can create a custom one by defining the parameters. Also, you can use an ‘array_to_string’ function to do the same job. Here’s how you can transform an SQL Array to a string using the ‘array_to_string’ function: 

Say you have an Array of strings: 
 

SELECT ARRAY[ ‘Black’, ‘White’, ‘Orange’] AS my_array;  It’ll look something like this:  my_array  -----------------------------  {Black, White, Orange}  Now, you can concatenate the elements using the ‘array_to_string’ function like this:  SELECT array_to_string(my_array, ‘,’) AS concatenated_string FROM (SELECT ARRAY[‘Black’, ‘White’, ‘Orange’] AS my_array) AS subquery;

 

Bear in mind that you’ll have to define the delimiter (“,”, “.”, “;”) to separate the elements from each other. Once done, your output will look something like this: 
 

concatenated_string  ------------------------------  Black, White, Orange

 

The second method involves creating a custom function, ‘ARRAY_JOIN’, here’s how you can do it: 

Let’s consider the same array as mentioned in the previous method. 
 

my_array  -----------------------------  {Black, White, Orange}

 

Here’s how you can create a custom function: 
 

CREATE OR REPLACE FUNCTION ARRAY_JOIN(arr anyarray, delimiter text)  RETURN text AS  $$  BEGIN  RETURN array_to_string(arr, delimiter);  END;  $$  LANGUAGE plpsql;  Here’s how you can use the custom function to create a string:  SELECT ARRAY_JOIN(my_array, ‘,’) AS concatenated_string FROM (SELECT ARRAY[‘Black’, ‘White’, ‘Orange’] AS my_array) AS subquery;

 

The output will look something like this: 
 

concatenated_string  ------------------------------  Black, White, Orange

 

5) array_length 

The problem of finding the SQL Array length can be solved by using ‘array_length’. This function takes into account an Array and its dimension. A dimension here refers to the layers of an Array. If used in a one-dimensional Array, it’ll give you the number of elements in that Array. If used in a multi-dimensional Array, though, it’ll give you the length of a specific dimension. 

If you have a one-dimensional Array, here’s how you can find its length: 
 

SELECT array_length(ARRAY[5, 10 15, 20], 1) AS array_length;

 

Where 1 is the dimension of the array. 

You’ll get: 
 

array_length  -------------------  4

4 is the number of elements in the aforementioned Array. 

If you have a multi-dimensional Array, here’s how you can find its length: 
 

SELECT array_length(ARRAY[[1,2,3], [4,5,6], [7,8,9]], 2) AS array_length;

 

Where 2 is the dimension of the Array. 

You’ll get: 
 

array_length  ---------------------  3

 

6) ARRAY_PREPEND 

As the name suggests, the ‘ARRAY_PREPEND’ function allows you to prepend a value into the Array without modifying the original one. Here’s how it works: 

Suppose you have an Array with the elements {2,3,4}, and you wish to add 1 to the beginning. All you have to do is: 
 

SELECT ARRAY_PREPEND(1, ARRAY[2,3,4]) AS new_array;

 

This will give you the following result: 
 

new_array  ---------------  {1,2,3,4}

 

Learn to write query with our Writing Analytical Queries for Business Intelligence 55232A Training today! 

7) ARRAY_CONTAINS 

This is a unique function as it can be represented with an operator and can also be created as a custom function. The ‘ARRAY_CONTAINS’ function is used in instances where you wish to know whether an Array contains elements from another Array. The answers, for the most part, are boolean values (TRUE, FALSE). 

If you want to create a custom function named ‘ARRAY_CONTAINS, here’s what you have to do: 
 

CREATE OR REPLACE FUNCTION ARRAY_CONTAINS(arr anyarray, elem anyelement)  RETURNS BOOLEAN AS  $$  BEGIN  RETURN elem = ANY(arr);  END;  $$  LANGUAGE plpgsql;

 

Now, say you have an Array {2,4,6,8}, and you wish to know whether element 4 exists in the Array or not. Here’s what you have to do: Now, say you have an Array {2,4,6,8}, and you wish to know whether element 4 exists in the Array or not. Here’s what you have to do: 
 

SELECT ARRAY_CONTAINS(ARRAY[2,4,6,8], 4) AS element_exists;

 

Here, you’ll receive the following output: 
 

element_exists  --------------------  TRUE

 

The second method involves using the ‘@>’ operator. Here’s how it works: 
 

SELECT ARRAY[2,4,6,8] AS my_array;

The Array shall remain the same from the previous example. Upon deploying ‘@>’ this happens: 
 

SELECT ARRAY[2,4,6,8] @> ARRAY[4] AS element_exists;

 

The output turns out to be: 
 

element_exists  --------------------  TRUE

 

8) ELEMENT_AT 

ELEMENT_AT follows the same trajectory as that of the aforementioned function. You can find the location of an element in an Array using this function. You can either create a custom ‘ELEMENT_AT’ function, or you can find an element using Array indexing. 

If you want to access an element using Array indexing, here’s what you have to do: 

Say you have an Array that looks like this, {1,3,5,7}, and you wish to find the element at position 3. Using Array indexing, here’s how you can do it: 
 

SELECT ARRAY[1,3,5,7] AS my_array;

 

Which gives the following Array: 
 

my_array  --------  {1,3,5,7}

 

Now, here’s what Array indexing does: 
 

SELECT my_array[3] AS element_at_position_3 FROM (SELECT ARRAY[1,3,5,7] AS my_array) AS subquery;

 

This will give you the following output: 
 

element_at_position_3  -------------------------------  5

 

You can do the same by creating a custom function like this: 
 

CREATE OR REPLACE FUNCTION ELEMENT_AT(arr anyarray, position integer)  RETURNS anyelement AS  $$  BEGIN  RETURN arr[position];  END;  $$  LANGUAGE plpgsql;

 

Now that the custom function has been created, here’s how you can use it: 
 

SELECT ELEMENT_AT(ARRAY[1,3,5,7], 3) AS element_at_position_3;

 

This will give you the following output: 
 

element_at_position_3  -------------------------------  5

Conclusion 

We hope you understand What SQL Arrays are and  what their functions are.. SQL Arrays provide a strong method for storing and handling data collections in a single column, allowing for more complex data structures and making certain tasks easier. Nevertheless, there are limitations to their usage, including potential performance drawbacks, difficulties in querying and indexing, and decreased data normalisation. Moreover, the level of support for arrays differs among various SQL Databases, impacting their ability to be easily transferred. 

Understand the SSIS Architecture with our SQL Server Integration Services 55321AC Training today!