How to sort a two-dimensional int array in PostgreSQL?

Question

{{5,23}, {8,45}, {1,12}} 

I want to sort this array by the first element of each submatrix element, for example:

 {{ 1 ,12}, { 5 ,23}, { 8 ,45}} 

How can i do this?

Edit:

This code works;

 create or replace function arraysortingaccordingfirstindexofsubarrayelements() returns void as $$ declare samplearraydata integer[][]; declare sortedarraydata int[][]; begin samplearraydata:=ARRAY[[5,8], [1,6],[3,9]]; EXECUTE 'CREATE TEMP TABLE temptable ( firstindex integer, secondindex integer ) on commit drop;'; WITH data as (select samplearraydata as arr) insert into temptable select arr[i][1], arr[i][2] FROM data, generate_subscripts((SELECT arr FROM data), 1) i order by 1; sortedarraydata:=(SELECT array_agg_mult(ARRAY[ARRAY[y.firstindex, y.secondindex]])) FROM temptable y; raise notice '%', sortedarraydata; end; $$ language plpgsql; CREATE AGGREGATE array_agg_mult (anyarray) ( SFUNC = array_cat ,STYPE = anyarray ,INITCOND = '{}' ); CREATE TEMP TABLE arrtbl ( firstindex integer, secondindex integer ) on commit drop; 

Credits for Erwin :)

Answer 1

Aggregation function

The aggregate aggregate function array_agg() currently (Postgres 9.4) only works for input types other than array. Since we're going to combine arrays, we need a custom aggregate function, as detailed in this related answer:

• Selecting data in a Postgres array

 CREATE AGGREGATE array_agg_mult (anyarray) ( SFUNC = array_cat ,STYPE = anyarray ,INITCOND = '{}' ); 

Upcoming Postgres 9.5 will ship an inline version of this aggregate function (which is significantly faster). Developer Documentation:

 Function Argument Type(s) Return Type array_agg(expression) any array type same as argument data type Description input arrays concatenated into array of one higher dimension (inputs must all have same dimensionality, and cannot be empty or NULL) 

Array access

A basic understanding of array syntax / array access is required. Read this chapter of the manual if you are not already.

Query

Then, based on the same setup as this earlier answer today (you can also read this too):

• Select every first element of an array of whole arrays into an array

 SELECT arrtbl_id , array_agg_mult(arr[i:i][lo2:up2] ORDER BY arr[i:i][lo2]) FROM (SELECT arrtbl_id, arr , array_lower(arr,2) AS lo2 , array_upper(arr,2) AS up2 FROM arrtbl) t , generate_subscripts(t.arr,1) i GROUP BY 1; 

Explanation

• Calculate the lower and upper bounds for measuring the second array in the base table, which is cheaper than repeating it for each array.

  I'm not just starting at index 1 to cover a possible corner case. Postgres allows you to use custom array indexes:

  • Normalize array indices for a 1-dimensional array so that they start at 1

• LATERAL attach the base table to generate_subscripts(arr,1) to get the first index for each submatrix (no matter how many dimensions).

• The expression arr[i:i][lo2:up2] returns the dimensions of the saved slice of the array. This one works for any number of elements and sizes (more than 1).

• The expression arr[i:i][lo2:lo2] returns the first slice in each slice of the array, which determines the sort order. For strictly 2-dimensional matrices, you can use arr[i][lo2] to return the first element of each slice, but the first works for any dimension greater than 1.

• A combination with array_agg_mult() , which happily accepts values ​​of the appropriate size (everything fits well). Or use the built-in array_agg() for arrays in pg 9.5+.

Answer 2

Since Postgres 9.4, if you can split your two-dimensional array into two one-dimensional arrays, you can use the operator

 unnest(anyarray, anyarray [, ...]) 

Example:

 SELECT * FROM unnest(ARRAY[6,4,3,5], ARRAY[64,2,1,-1], ARRAY[45,2,4,6]) ORDER BY 1 

Return:

 4 2 2 3 1 4 5 -1 6 6 64 45 

If you want to sort by three elements, you must change

 ... ORDER BY 3