Get Nth element of an array that returns from "string_to_array()" function

*Memos:

• An array has values from [1] but not from [0] so [0] returns NULL.

• Basically, you should use type conversion to create an array except when you declare a non-empty array in a DECLARE clause in a function, procedure or DO statement because the type may be different from your expectation and there is some case which you cannot create an array without type conversion. *My answer explains type conversion in detail.

• The doc explains arrays in detail.

• My answer explains how to create and use the 1D(one-dimensional) array with VARCHAR[] in detail.

• [My post][4] explains how to create and use a 2D(two-dimensional) array in detail.

• My answer explains how to create and use an empty array in detail.

You can get array's elements using string_to_array() with these ways below:

SELECT string_to_array('1,2,3,4,5', ',')::INT[]; -- {1,2,3,4,5}
SELECT (string_to_array('1,2,3,4,5', ',')::INT[])[0]; -- NULL
SELECT (string_to_array('1,2,3,4,5', ',')::INT[])[2]; -- 2
SELECT (string_to_array('1,2,3,4,5', ',')::INT[])[2:4]; -- {2,3,4}
SELECT (string_to_array('1,2,3,4,5', ',')::INT[])[:4]; -- {1,2,3,4}
SELECT (string_to_array('1,2,3,4,5', ',')::INT[])[2:]; -- {2,3,4,5}
SELECT (string_to_array('1,2,3,4,5', ',')::INT[])[:]; -- {1,2,3,4,5}

*Memos:

• The type of the array above is INT[](INTEGER[]).

• If you omit ::INT[], the array above is TEXT[] type which cannot do calculation.

• Don't put any spaces in the 1st argument of string_to_array() otherwise the values separated by , have spaces.

In addition, you can get array's elements with these ways below:

SELECT ARRAY[1,2,3,4,5]::INT[]; -- {1,2,3,4,5}
SELECT (ARRAY[1,2,3,4,5]::INT[])[0]; -- NULL
SELECT (ARRAY[1,2,3,4,5]::INT[])[2]; -- 2
SELECT (ARRAY[1,2,3,4,5]::INT[])[2:4]; -- {2,3,4}
SELECT (ARRAY[1,2,3,4,5]::INT[])[:4]; -- {1,2,3,4}
SELECT (ARRAY[1,2,3,4,5]::INT[])[2:]; -- {2,3,4,5}
SELECT (ARRAY[1,2,3,4,5]::INT[])[:]; -- {1,2,3,4,5}

*Memos:

• The type of the array above is INT[](INTEGER[]).

• Even if you omit ::INT[], the type of the array above is still INT[].

Or:

SELECT '{1,2,3,4,5}'::INT[]; -- {1,2,3,4,5}
SELECT ('{1,2,3,4,5}'::INT[])[0]; -- NULL
SELECT ('{1,2,3,4,5}'::INT[])[2]; -- 2
SELECT ('{1,2,3,4,5}'::INT[])[2:4]; -- {2,3,4}
SELECT ('{1,2,3,4,5}'::INT[])[:4]; -- {1,2,3,4}
SELECT ('{1,2,3,4,5}'::INT[])[2:]; -- {2,3,4,5}
SELECT ('{1,2,3,4,5}'::INT[])[:]; -- {1,2,3,4,5}

*Memos:

• The type of the array above is INT[](INTEGER[]).

• If you omit ::INT[], the value above is not an array and the type of the value above is unknown.

And, even if you set INT[2] to the array, the result is the same as shown below:

postgres=# SELECT ARRAY[1,2,3,4,5]::INT[2];
    array
-------------
 {1,2,3,4,5}
(1 row)

And, the type of the array is INT[](INTEGER[]) rather than INT[2](INTEGER[2]) as shown below. *You can use pg_typeof() to check the type of a value:

postgres=# SELECT pg_typeof(ARRAY[1,2,3,4,5]::INT[2]);
 pg_typeof
-----------
 integer[]
(1 row)

And, even if you set VARCHAR to 1, the type of 1 is INT(INTEGER) rather than VARCHAR as shown below because the outer type conversion INT[] is prioritized:

postgres=# SELECT (ARRAY[1::VARCHAR,2,3,4,5]::INT[])[1];
 array
-------
     1
(1 row)

postgres=# SELECT pg_typeof((ARRAY[1::VARCHAR,2,3,4,5]::INT[])[1]);
 pg_typeof
-----------
 integer
(1 row)

To add the example of what I was looking for:

select product_ids[1] from comments where board_name = '12345678';