PostgreSQL - Extract value out of an array with position

Question

I want to extract a value out of an array of numerical values with index position in PostgreSQL.

My array is like {0.10,0.20,0.30}, type is numeric[], array position is an integer calculated with array_position. Syntax will be SELECT myarray[array_position];, but when I try (for example) :

SELECT'{0.10,0.20,0.30}'::numeric[][1];

It returned me the entire array.

How to extract a value out of an array with index position in PostgreSQL?

Amit · Accepted Answer · 2021-06-16 13:02:09Z

7

In your example [1] is the part of array definition: https://www.postgresql.org/docs/current/arrays.html#ARRAYS-DECLARATION

However, the current implementation ignores any supplied array size limits, i.e., the behavior is the same as for arrays of unspecified length.

Use parentheses to split the definition and reference:

SELECT ('{0.10,0.20,0.30}'::numeric[])[1];

edited Jun 16, 2021 at 13:02

Amit

6891 gold badge9 silver badges27 bronze badges

answered Jun 16, 2021 at 12:53

Abelisto

15.8k3 gold badges38 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

GeoGyro Over a year ago

I don't know I have to split definition and reference with parentheses but it's logic when you write it.

Collectives™ on Stack Overflow

PostgreSQL - Extract value out of an array with position

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related