Why do PostgreSQL arrays have different starting indices for 1D and 2D arrays? I'm working with point data in the form (p1, p2), which is represented in text as ((x, y), (x, y)). I would like to get 4 values out of this string (x1, x2, y1, y2), But I'm having trouble working with array indices.

For example:

(1) Get a point[] representing the 2 points

select concat('{', replace(replace('(64,38),(-26,-40)', '(', '"('), ')', ')"'), '}')::point[];

-- This correctly gives me {(64.0,38.0),(-26.0,-40.0)}


(2) Get the first point from that array

select ( select concat('{', replace(replace('(64,38),(-26,-40)', '(', '"('), ')', ')"'), '}')::point[] )[1]

-- this gives me (64.0,38.0) (i.e. index = 1 gives me the FIRST point)


(3) Get the x value of the first point

select ( select ( select concat('{', replace(replace('(64,38),(-26,-40)', '(', '"('), ')', ')"'), '}')::point[] )[1] )[0]

-- This gives me 64 (i.e. index = 0 gives me the FIRST point)


It seems that in a 2D array, indices start at 1. But in a 1D array, indices start at 0. How can this be????

I've tested this on Dbeaver and separately on PG Admin 4 and I get the same result.
You don't have a two-dimensional array, you have an one dimensional array of points.  That is not the same thing, even if they do have some similarities.  I think a "point" is a type implemented as a fixed length array type, as discussed in the docs


  For historical reasons (i.e., this is clearly wrong but it's far too late to change it), subscripting of fixed-length array types starts from zero, rather than from one as for variable-length arrays.