An array is a data structure that stores a collection of values in a single entity. In SQL, an array can be used to store multiple values in a single column of a table.
There are various operations that can be performed on arrays in SQL, such as:
- Declaring an array: An array can be declared in SQL using the
ARRAY
keyword. The syntax for declaring an array is:ARRAY[element1, element2, ...]
. - Accessing elements in an array: The elements of an array can be accessed using the
[]
operator. For example, to access the second element of an array namedmy_array
, you can usemy_array[2]
. - Adding elements to an array: Elements can be added to an array using the
||
operator. For example, to add an element'new_element'
to an array namedmy_array
, you can usemy_array || 'new_element'
. - Removing elements from an array: Elements can be removed from an array using the
-
operator. For example, to remove an element'old_element'
from an array namedmy_array
, you can usemy_array - 'old_element'
. - Sorting an array: The
ORDER BY
clause can be used to sort the elements of an array. The syntax for sorting an array is:SELECT * FROM table_name ORDER BY column_name ASC|DESC
. - Searching for an element in an array: The
IN
operator can be used to search for an element in an array. The syntax for searching for an element in an array is:SELECT * FROM table_name WHERE column_name IN (element1, element2, ...)
.
Here is an example of how you can use arrays in SQL:
CREATE TABLE users (
id INT PRIMARY KEY,
name VARCHAR(255),
hobbies ARRAY[VARCHAR(255)]
);
INSERT INTO users (id, name, hobbies)
VALUES (1, 'John', ARRAY['soccer', 'swimming']),
(2, 'Jane', ARRAY['reading', 'painting']);
SELECT * FROM users WHERE hobbies IN ('soccer', 'swimming');
This will return the following result:
id | name | hobbies
---|-------|------------------
1 | John | {soccer, swimming}