Is there a type for empty array in sql

Question

Does ANSI sql have any implementation details on what the type is of an array with zero non-null elements? Some examples:

[]
[null, null]
[[], [], []]
[[null],[null]]

If not, is there any agreement on how it should be implemented? As an example Postgres gives an error on an empty array (makes sense) but casts it to text on an array with only null elements (doesn't make sense):

postgres=# select pg_typeof(array[]);
ERROR:  cannot determine type of empty array
HINT:  Explicitly cast to the desired type, for example ARRAY[]::integer[].

postgres=# select pg_typeof(array[null]);
 pg_typeof 
-----------
 text[]

My thinking was to return something like "undefined" instead of an error, but I wonder if that is a valid approach or not.

Answer 1

In ANSI SQL the array was introduced in SQL99 standard version, and then it was extended in SQL2003 and on.

Best to my knowledge, there is no direct answer to your question in standard docs. I mean, the standard has no restriction over what type of array should be [NULL, NULL] for instance.

I would say, if you can, do not count on ANSI SQL please. I mean, that ANSI SQL in general is somewhat hypothetical. At least, I do not know a single vendor that fully implement the SQL standard (well, maybe, but not actually, except MS SQL Server I guess), especially in such a particular and concrete parts. So even if you have found something in ANSI standard, I would not count on it.