Why are parentheses required for array indexing in postgres?

Question

In (postgres) SQL, this is valid syntax,

select (array[ [1,2,3], [4,5,6]])[1];

but not:

select array[ [1,2,3], [4,5,6]][1];

... which is a little surprising to me. What's going on here?

Strange, select array[ [1,2,3], [4,5,6]]::int[][1]; returns the full array. Which also seems like unpredictable behavior. — ced-b
– ced-b, Commented Jun 9, 2017 at 0:04

Pavel Stehule · Accepted Answer · 2017-06-09 06:22:34Z

2

This is a parser issue. PostgreSQL uses Bison parser - additional parenthesis are required as protection against parsing collisions. The query processing is in stages: parsing, analyse, planning and execution. There are not enough information in parsing time to protect bison against possible collision and then some syntaxes are not disallowed.

Because a arrays was proprietary PostgreSQL feature (very successful) and syntax was not described by ANSI, a developers preferred some compromise design like good enough for users and doesn't require too complex code. A focus of PostgreSQL is in SQL and not in arrays processing. The Bison is very good mature tool, but sometimes due speed requirements some syntaxes are difficult or impossible.

select array[ [1,2,3], [4,5,6]]::int[][1]

There is not any magic if you know a fact, so limits in arrays types definition are syntactic sugar only. int[],int[1],int[] [100]is just int[]. Then array[ [1,2,3], [4,5,6]]::int[][1] is same like array[ [1,2,3], [4,5,6]]::int[] .. take a numeric array and cast it to integer array.

edited Jun 9, 2017 at 6:22

answered Jun 9, 2017 at 4:20

Pavel Stehule

46.6k6 gold badges103 silver badges102 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user330315 Over a year ago

"Because a arrays are proprietary PostgreSQL feature" that's not true. Arrays are defined by the SQL standard. And e.g. foo[1] or array[1,2,3,] are both valid standard SQL. The standard however does only defines one-dimensional arrays.

Pavel Stehule Over a year ago

@a_horse_with_no_name - thank you for fix, I didn't know it. I though so only sets are supported without special syntax (I corrected the text).

Collectives™ on Stack Overflow

Why are parentheses required for array indexing in postgres?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related