How to select 1d array from 2d array?

Question

I have a 2d array and want to select only its first element, which is a 1d array.
How can i do this?

Answer 1

To get the first fragment of an array :

SELECT my_arr[1:1]; 

The resulting array has the same array sizes as the input.
Details in my previous answer here:

• Expand an array one level

To smooth the result:

 SELECT ARRAY(SELECT unnest(my_arr[1:1])); 

Or cleaner:

 SELECT ARRAY(SELECT * FROM unnest(my_arr)[1:1])); 

Examples

 SELECT (ARRAY[[1,2,3], [4,5,6], [7,8,9]])[1:1]; 

Result:

 {{1,2,3}} -- 2D array 

Or:

 SELECT ARRAY( SELECT unnest((ARRAY[[1,2,3], [4,5,6], [7,8,9]])[1:1]) ); 

Result:

 {1,2,3} -- 1D array 

unnest() in Postgres 8.3

Reply to your comment:
The wiki page that you link to is misleading. I updated it with code for 2-dimensional arrays.

unnest() for a one-dimensional array:

 CREATE OR REPLACE FUNCTION unnest_1d(anyarray) RETURNS SETOF anyelement AS $func$ SELECT $1[i] FROM generate_series(array_lower($1,1), array_upper($1,1)) i $func$ LANGUAGE sql IMMUTABLE; 

unnest() for a 2-dimensional array:

 CREATE OR REPLACE FUNCTION unnest_2d(anyarray) RETURNS SETOF anyelement AS $func$ SELECT $1[d1][d2] FROM generate_series(array_lower($1,1), array_upper($1,1)) d1 , generate_series(array_lower($1,2), array_upper($1,2)) d2 $func$ LANGUAGE sql IMMUTABLE; 

The aggregate function array_agg() not installed by default in Postgres 8.3:

 CREATE AGGREGATE array_agg(anyelement) ( SFUNC = array_append, STYPE = anyarray, INITCOND = '{}' ); 

Map 2d array to 1d arrays:

 CREATE OR REPLACE FUNCTION unnest_2d_1d(anyarray) RETURNS SETOF anyarray AS $func$ SELECT array_agg($1[d1][d2]) FROM generate_series(array_lower($1,1), array_upper($1,1)) d1 , generate_series(array_lower($1,2), array_upper($1,2)) d2 GROUP BY d1 ORDER BY d1 $func$ LANGUAGE sql IMMUTABLE; 

SQL Fiddle