CODEWITHSUNDEEP
arrayspostgresql
Alexound
Alexound

Reputation: 133

PostgreSQL: retrieving multiple array elements

Let's say we have a query like:

SELECT regexp_split_to_array('foo,bar', ',');

Results:

+-----------------------+
| regexp_split_to_array |
+-----------------------+
| {foo,bar}             |
+-----------------------+
(1 row)

To access a single element of an array we can use code like:

SELECT (regexp_split_to_array('foo,bar', ','))[1];

Which will return:

+-----------------------+
| regexp_split_to_array |
+-----------------------+
| foo                   |
+-----------------------+
(1 row)

Or use slices like:

SELECT (regexp_split_to_array('foo,bar', ','))[2:];

Result:

+-----------------------+
| regexp_split_to_array |
+-----------------------+
| {bar}                 |
+-----------------------+
(1 row)

However, when I try to access 2 elements at once, like:

SELECT (regexp_split_to_array('foo,bar', ','))[1,2];

or

SELECT (regexp_split_to_array('foo,bar', ','))[1][2];

or any other syntax, I receive an error:

ERROR:  syntax error at or near ","

Is it possible to retrieve two different and not adjacent elements of an array in PostgreSQL?

Upvotes: 1

Views: 2781

Answers (2)

Hambone
Hambone

Reputation: 16407

The colon ':' in the array indexer does allow you to access multiple elements as a from-thru.

select (array[1,2,3,4,5])[2:4]

returns

{2,3,4}

This would work in your example above, but not if 1 an 2 weren't next to each other. If that's the case, the suggestion from @KaushikNayak is the only way I could think of.

Using your example:

SELECT (regexp_split_to_array('foo,bar', ','))[1:2]

Upvotes: 0

Kaushik Nayak
Kaushik Nayak

Reputation: 31726

Extracting multiple elements through a select from an array should either mean you can have them returned as multiple columns or all those elements part of a single array.

This returns you one column as an array of the two elements. 

knayak=# select ARRAY[arr[1],arr[2]] FROM regexp_split_to_array('foo,bar', ',') as arr;
   array
-----------
 {foo,bar}
(1 row)

..and this simply gives you the two elements as columns.

knayak=# select arr[1],arr[2] FROM regexp_split_to_array('foo,bar', ',') as arr;
 arr | arr
-----+-----
 foo | bar
(1 row)

Upvotes: 2

Related Questions