PostgreSQL Arrays – Querying for Array[0] with 1-Based Arrays

arraypostgresqlpostgresql-9.3

I have been playing with arrays in one of my PostgreSQL databases.

I have created a table with a geometry array with at least one element:

CREATE TABLE test_arrays (
    polygons geometry(Polygon,4326)[],
    CONSTRAINT non_empty_polygons_chk 
        CHECK ((COALESCE(array_length(polygons, 1), 0) <> 0))
);

I have added a few rows, and queried the table for the first element in each geometry array:

SELECT polygons[0] FROM test_arrays;

To my surprise, I got a list of empty rows!

After some research, it turns out that PostgreSQL arrays are one-based:

The array subscript numbers are written within square brackets. By default PostgreSQL uses a one-based numbering convention for arrays, that is, an array of n elements starts with array[1] and ends with array[n].

So SELECT polygons[0] FROM test_arrays; worked and returned the first polygon of each row.

If PostgreSQL uses a one-based numbering convention, why is querying the 0-th element allowed, and does the result have any meaning?

Best Answer

Postgres arrays are 1-based by default. And in typical applications it's best to stick with the default. But the syntax allows to start with any integer number. The documentation:

Subscripted assignment allows creation of arrays that do not use one-based subscripts. For example one might assign to myarray[-2:7] to create an array with subscript values from -2 to 7.

Examples:

SELECT ('[0:2]={1,2,3}'::int[])[0];    --> 1
SELECT ('[-7:-5]={1,2,3}'::int[])[-6]; --> 2

You can query any index of an array, if it does not exist you get NULL.

Normalize array subscripts for 1-dimensional array so they start with 1

Related Solutions

osm2pgsql – Select Relation Member by Role

If we can make the assumption that the unnest() operation will always return a set where a member "id" is listed followed by its role in a single column of text entries, then I've written up a quick solution for you.

I made a quick data set as:

CREATE TABLE mylist (entry text);

INSERT INTO mylist VALUES
('n240045827'),
('admin_centre'),
('w185481610'),
('outer'),
('w191987461'),
('outer'),
('w166286278'),
('outer'),
('w166290573'),
('outer'),
('w65481875'),
('outer'),
('w166278211'),
('outer'),
('w166278224'),
('outer'),
('w166278225'),
('outer'),
('w166278227'),
('outer'),
('w185148605'),
('outer');

Assuming we can return this, then the script

WITH numbered AS(
  SELECT row_number() OVER() AS row, entry
  FROM mylist)
  SELECT a.entry
  FROM numbered AS a JOIN numbered AS b
  ON a.row = b.row-1 AND b.entry = 'admin_centre';

will return only n240045827.

How does it work? Well, I just took a straightforward approach to assigning order to the returned list by using row_number() OVER(). In this example, I've used a CTE WITH... to add the row after the fact, but in your case you could add it into your query like so:

...
SELECT ltrim(member, 'n')::bigint AS osm_id
    FROM (
    SELECT row_number() OVER() a AS row, unnest(members) AS member
    FROM planet_osm_rels
...

Finally, I simply join this result to itself, comparing one row to the next by its row number, and looking for the 'admin_centre' text entry.

Here's a SQL Fiddle for testing.

For your perusal:

Common Table Expressions

Window Functions

Edit

If you want to return all rows as column pairs, then use the following approach:

WITH numbered AS(
  SELECT row_number() OVER() AS row, entry
  FROM mylist)
  SELECT a.entry, b.entry
  FROM numbered AS a JOIN numbered AS b
  ON a.row = b.row-1 AND a.row%2 = 1;

Also, here's a SQL Fiddle #2 for testing.

Postgresql – Array of template type in PL/pgSQL function using %TYPE

The %TYPE construct can only copy the exact type. But there is a simple workaround to register the according array type: Create a (temporary) table or view with the desired array type.

Template table:

CREATE TABLE foo (i int);

(Temporary) view to register the array type, optionally empty (LIMIT 0):

CREATE TEMP VIEW v_foo_i_arr AS
SELECT ARRAY (SELECT i FROM foo LIMIT 0) AS i_arr;

Or a (temporary) table:

CREATE TEMP TABLE foo_i_arr AS
SELECT ARRAY (SELECT i FROM foo LIMIT 0) AS i_arr;

Now your function works:

CREATE OR REPLACE FUNCTION f_arr_test(v_foo_i_arr.i_arr%TYPE)
  RETURNS foo.i%TYPE AS  -- example: return element type
$func$
BEGIN
   RETURN $1[2];
END
$func$  LANGUAGE plpgsql;

You might as well make that a temporary view or table, since the type in the function signature is converted to the underlying type immediately. No persisted connection to the used template. So it's no problem that temp objects are dropped at the end of the session.

Polymorphic type

You might be better off with a polymorphic type to begin with. This works for any array type:

CREATE OR REPLACE FUNCTION f_arr_test_polymorphic(ANYARRAY)
  RETURNS ANYELEMENT AS  -- derived from input type
$func$
BEGIN
   RETURN $1[2];
END
$func$  LANGUAGE plpgsql;

SQL Fiddle.

Note that using a %TYPE declaration inside the function body actually works dynamically in that it looks up the type in the system catalog at the first call of every session.