PostgreSQL: retrieving multiple array elements

Question

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?

Kaushik Nayak · Accepted Answer · 2018-10-21 16:27:53Z

2

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)

answered Oct 21, 2018 at 16:27

Kaushik Nayak

32k6 gold badges36 silver badges54 bronze badges

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

2 Comments

Alexound Over a year ago

Thanks, this is exactly what I was looking for.

Kaushik Nayak Over a year ago

@Alexound : you're welcome. If you found the answer please accept it. Accepted answers increases it's value and helps others seeking solution to the same problem.

Hambone · Accepted Answer · 2018-10-21 18:07:22Z

0

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]

answered Oct 21, 2018 at 18:07

Hambone

16.5k8 gold badges54 silver badges79 bronze badges

Collectives™ on Stack Overflow

PostgreSQL: retrieving multiple array elements

2 Answers 2

2 Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Related