In PostgreSQL, you can access elements within an array using array subscripting. PostgreSQL arrays are 1-based, meaning the first element of the array is at index 1, not 0.
we will exploring on how to access
- First element of array in PostgreSQL table
- second element of array in PostgreSQL table
- Last element of array in PostgreSQL table
- Multiple elements of the array at once in PostgreSQL table
Syntax – Access Array Elements in PostgreSQL:
array[index]
- array: The array from which you want to access an element.
- index: The 1-based position of the element you want to retrieve.
Examples – Access Array Elements in PostgreSQL:
Access the First Element of an Array:
To Accesses the first element of the array {‘Bread’, ‘Butter’, ‘Jam’} we will be using the Index of the array first index will denote the first position and so on.
SELECT (ARRAY['Bread', 'Butter', 'Jam'])[1] AS first_element;
Output:
Access the Third Element of an Array:
To Accesses the third element of the array {‘Bread’, ‘Butter’, ‘Jam’} we will be using the Index , Mentioning 3 in index will retrieve or access the Third position.
SELECT (ARRAY['Bread', 'Butter', 'Jam'])[3] AS third_element;
Output:
Access an Element in an Array Column in PostgreSQL:
You can extract Nth Element of the array column in PostgreSQL table.
Let’s use the table name “orders_arr_table”
In the array column, you can access its elements using array indexing. PostgreSQL arrays are 1-based, meaning the first element is at index 1.
Access First Element of Array Column in PostgreSQL
Example: To Access First Element of the Array, we mention the column name followed by indexing in our example , order_items[1] will give first item
SELECT * , order_items[1] AS first_item FROM orders_arr_table;
Output:
Access Second Element of Array Column in PostgreSQL
Example: To Access Second Element of the Array, we mention the column name followed by indexing in our example , order_items[2] will give second item
SELECT * , order_items[2] AS Second_item FROM orders_arr_table;
Output:
Access Last Element of Array Column in PostgreSQL
Example: To Access last Element of the Array, We use array_length() function to find the length of the array and pass it as the value for indexing
SELECT *, order_items[array_length(order_items, 1)] AS last_item FROM orders_arr_table;
Output:
Access Multiple Element of Array Column in PostgreSQL
Example: To Access Multiple Element of the Array, specific indexing values to extract nth array element and if want to extract last element we use array_length() function to find the length of the array and pass it as the value for indexing
SELECT *, order_items[1] AS first_item, order_items[2] AS second_item, order_items[array_length(order_items, 1)] AS last_item FROM orders_arr_table;
Output:
