Subscripts

Subscripts select element(s) from arrays:

• expression[subscript] for a specific element

• expression[lower_subscript:upper_subscript] for an array slice (multiple adjacent elements)

mydb=> SELECT products AS whole_array, products[1] AS first_element,
mydb->        products[2:3] AS second_and_third
mydb->   FROM suppliers
mydb->   WHERE array_length(products, 1) > 2;
        whole_array        | first_element |  second_and_third  
---------------------------+---------------+--------------------
 {Kiwis,Lemons,Mangoes}    | Kiwis         | {Lemons,Mangoes}
 {Apples,Mangoes,Tomatoes} | Apples        | {Mangoes,Tomatoes}
(2 rows)

subscript is rounded off to the nearest integer:

mydb=> SELECT products, products[0.75] AS sub_0_75, products[1] AS sub_1,
mydb->        products[1.25] AS sub_1_25
mydb->   FROM suppliers
mydb->   WHERE array_length(products, 1) > 1;
         products          |  sub_0_75  |   sub_1    |  sub_1_25  
---------------------------+------------+------------+------------
 {Apples,Cherries}         | Apples     | Apples     | Apples
 {Apples,Coconuts}         | Apples     | Apples     | Apples
 {Kiwis,Lemons,Mangoes}    | Kiwis      | Kiwis      | Kiwis
 {Apples,Mangoes,Tomatoes} | Apples     | Apples     | Apples
 {Pineapples,Watermelons}  | Pineapples | Pineapples | Pineapples
(5 rows)

The array expression should be in parenthesis e.g (expression)[1](but this can be omitted for column references or positional parameters).

Multiple subscripts can be concatenated if the array is multi-dimensional e.g expression[sub1][sub2].