32

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?

2
  • 3
    Read that page to the end: "Also, null is returned if a subscript is outside the array bounds (this case does not raise an error)"
    – user1822
    Oct 11, 2015 at 9:54
  • @a_horse_with_no_name True and thank-you. I guess that the this design principle is intended to stop queries for raising errors and ending transactions due to index mismatches.
    – Adam Matan
    Oct 11, 2015 at 9:56

1 Answer 1

32

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 manual:

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.

Related:

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.