Arrays

In PostgreSQL, defining an array is a simple process. An array is specified by adding square brackets to a valid data type. This could range from an array of integers, text, boolean values, etc.

CREATE TABLE my_table (
    id SERIAL PRIMARY KEY,
    my_array INT[]
);

In this case, my_array is an array of integers. This column can now store multiple integer values.

Insert Records

To initialize an array with values, one can utilize the ARRAY constructor. Below is an illustration demonstrating the process of inserting data into the table that has just been created:

INSERT INTO my_table (my_array) VALUES (ARRAY[1, 2, 3, 4, 5]);

View Records

To access individual elements in an array, you use square brackets. Remember, arrays in PostgreSQL are 1-indexed, meaning the first element is at position 1. Here's an example:

SELECT my_array[1] FROM my_table WHERE id = 1;

Functions

array_append(array, element): This function appends an element to the end of an array

SELECT array_append(ARRAY[1, 2, 3], 4);

//This will result in ARRAY[1, 2, 3, 4].

array_prepend(element, array): This function adds an element to the beginning of an array.

SELECT array_prepend(0, ARRAY[1, 2, 3]);

//This will result in ARRAY[0, 1, 2, 3].

array_length(array, dimension): This function returns the length of the requested array dimension.

SELECT array_length(ARRAY[1, 2, 3], 1);

//This will return 3 because there are three elements in the first dimension of the array.

unnest(array): This function "flattens" an array into a set of rows.

SELECT unnest(ARRAY[1, 2, 3]);

//This will return three rows with the values 1, 2, and 3 respectively.

Querying Array

The ANY keyword allows you to check if any element in an array matches a condition.

SELECT * FROM my_table WHERE 3 = ANY(my_array);

This statement returns all rows where my_array contains the value 3.

The ALL keyword is used to check if all elements in an array satisfy a condition.

SELECT * FROM my_table WHERE 3 > ALL(my_array);

This statement returns all rows where all values in my_array are less than 3.