How do I conditionally select elements of an array? I have a table (only one row) in my PostgreSQL 9.5 db with two columns i.e., count (bigint) and array (text).

count     array
6         "112,19.3,142,142,19.3,172,172,20.3,202,202,20.3,232,232,19.3,262,262,19.3,292"


The array represents six sets of values i.e., Lower_limit, height and Upper_limit.How? every three elements of array make the set as 1 thus 18 elements / 3 = 6 sets (count). Now, I need to conditionally modify my array i.e., when upper limit and lower limits are coinciding AND if the difference among values is less than or equal to 1 then merge those sets and select the first upper limit and last lower limit and return the most common value (which is 19.3) among the limits. My desired output would be like:

count    array
1        112, 19.3, 292


Could anyone help me to have some pointers towards my desired output?

UPDATE1:

The array is formed from a series of three members/elements i.e., lower limit, value and upper limit. Whenever, these three values appear in array elements it means we have 1 set (count = 1). In the question, there are 18 elements in array >> 18 / 3 = 6 sets (or count = 6). It can be considered as counting the number of elements in array (3 elements make one set i.e., foo, value, bar >> 1 set). I hope things are clear and I may replace "count" by "set" in question if it creates confusion.

Now my requirement was:


Starting from the first set, check if the upper limit of first set coincides with the next AND 
if the difference among values (19.3 and 19.3) is less than or equal to 1
If both or any of above condition fulfills, then merge them and get limits of merged ones
Do the same for all sets and get the lower and upper limit of finally merged set. Count the elements  of merged set and update "counter/set"




In the given array, the difference among values is less than or equal to 1 and also starting from 1st set (112, 19.3, 142)to second set (142,19.3, 172), the upper limit of first set coincides with the lower limit of second set means there are next to each other and also the difference between their values is 0 thus they should be merged. In this manner, I needed to have my desired output. Hope things are clear and make sense now.
I figured out your question by brute force. Please give more details next time. I still have no idea what count() is for. You want the min(), max() and mode() from an array. We can do that by splitting out the text value with string_to_array. Then we can unnest the array to get something we can run our aggregate functions on.

Note, mode is a ordered set aggregate. That's why it looks different.

From there the job is simple.

CREATE TABLE foo(v) AS 
VALUES ('112,19.3,142,142,19.3,172,172,20.3,202,202,20.3,232,232,19.3,262,262,19.3,292');

SELECT
  min(x),
  mode() WITHIN GROUP (ORDER BY x),
  max(x)
FROM foo
CROSS JOIN LATERAL unnest(string_to_array(v, ',')) AS x;

 min | mode | max 
-----+------+-----
 112 | 19.3 | 292
(1 row)


Or, if you want that in an array..

SELECT ARRAY[
  min(x),
  mode() WITHIN GROUP (ORDER BY x),
  max(x)
]
FROM foo
CROSS JOIN LATERAL unnest(string_to_array(v, ',')) AS x;

     array      
----------------
 {112,19.3,292}
(1 row)