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.
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)