PostgreSQL fails on empty constructor arrays

Question

I have encountered a strange situation with arrays, which looks like a bug in PostgreSQL, unless I'm missing something...

According to PostgreSQL documentation, constructor and string presentations of arrays are interchangeable, i.e. we can either write ARRAY[1,2,3] or '{1,2,3}', which is the same.

However, I have found one case when they are not treated the same.

I am using an automatic SQL generator for multi-row updates that spits out the following:

UPDATE "myTable" AS t SET "data"=v."data"::int[] FROM (VALUES(1, array[]))
AS v("id", "data") WHERE t.id=v.id

The table is as follows:

CREATE TABLE myTable(
  id serial PRIMARY KEY,
  data int[] NULL
);

Executing that query produces error - cannot determine type of empty array, even though we are clearly casting the column type.

And if I replace array[] with the equivalent '{}', then it suddenly works.

I've never seen this happen before, this is first time, perhaps a unique situation, but from what I see, it goes against PostgreSQL documentation for interchangeable array presentation.

---

More examples, to explain the issue:

These work:

UPDATE "myTable" AS t SET "data"=v."data"::int[] FROM (VALUES(1, array[1,2,3]))
AS v("id", "data") WHERE t.id=v.id

UPDATE "myTable" AS t SET "data"=v."data"::int[] FROM (VALUES(1, '{1,2,3}'))
AS v("id", "data") WHERE t.id=v.id

UPDATE "myTable" AS t SET "data"=v."data"::int[] FROM (VALUES(1, '{}'))
AS v("id", "data") WHERE t.id=v.id

This one doesn't work:

UPDATE "myTable" AS t SET "data"=v."data"::int[] FROM (VALUES(1, array[]))
AS v("id", "data") WHERE t.id=v.id

So the type casting for array constructor works, for as long as the array isn't empty, just as it is empty, then the type casting stops working.

Answer 1

It is supported with type info:

pokus1=# select array[];
ERROR:  cannot determine type of empty array
LINE 1: select array[];
               ^
HINT:  Explicitly cast to the desired type, for example ARRAY[]::integer[].
pokus1=# select array[]::integer[];
  array
 -------
  {}
 (1 row)