ScalarArrayOpExpr and multi-dimensional arrays
Hackorum builds and tests every patch posted to the lists, not only commitfest submissions. This is Hackorum's own CI rather than the PostgreSQL project's, and it is still under testing - please report anything that looks wrong.
You can run a PostgreSQL built from this patch straight from Docker, with no checkout and no build:
docker run --rm -p 5432:5432 ghcr.io/hackorum-dev/postgres-patch:t37834psql -h localhost -U postgresBuilt from patchset v3 (message #3), July 27, 2026 at 09:40 PM.
Every patchset is also pushed to a branch of our PostgreSQL fork, so you can check out the same tree CI built. Without a PostgreSQL checkout:
git clone --branch t37834_3 https://github.com/hackorum-dev/postgres.gitIn a checkout you already have, add the fork once:
git remote add hackorum https://github.com/hackorum-dev/postgres.gitthen, for this patchset and every later one:
git fetch hackorum t37834_3 && git checkout t37834_3Patchset v3 (message #3) is on t37834_3
Hi.
I wonder if ScalarArrayOpExpr is not really meant for multi-dimensional
arrays appearing on the right hand side? Because:
# select array[1] = any (array[array[1], array[2]]);
ERROR: operator does not exist: integer[] = integer
LINE 1: select array[1] = any (array[array[1], array[2]]);
^
HINT: No operator matches the given name and argument types. You might
need to add explicit type casts.
I noticed this when looking at the constraint of a list partitioned table
on a int[] column.
create table p (a int[]) partition by list (a);
create table p1 partition of p for values in ('{1}');
\d+ p1
...
Partition of: p FOR VALUES IN ('{1}')
Partition constraint: ((a IS NOT NULL) AND ((a)::anyarray
OPERATOR(pg_catalog.=) ANY (ARRAY['{1}'::integer[]])))
I got the same error as above when I try to put that ANY expression in a
query:
select (a)::anyarray OPERATOR(pg_catalog.=) ANY (ARRAY['{1}'::integer[]])
from p;
ERROR: operator does not exist: integer[] pg_catalog.= integer
I guess we shouldn't be generating such a constraint expression if backend
is not going to accept the same. Or should ScalarArrayOpExpr be made to
sanely process multi-dimensional arrays appearing on the right hand side?
Thanks,
Amit
Amit Langote <Langote_Amit_f8@lab.ntt.co.jp> writes:
I wonder if ScalarArrayOpExpr is not really meant for multi-dimensional
arrays appearing on the right hand side? Because:
# select array[1] = any (array[array[1], array[2]]);
ERROR: operator does not exist: integer[] = integer
You are falling into the misimpression that a 2-D array is an array of
1-D arrays. It is not, even if the syntax makes it look like that.
ScalarArrayOpExpr just iterates over the array elements without regard
to dimensionality; so the LHS must be of the element type.
regards, tom lane
On 2017/12/08 23:34, Tom Lane wrote:
Amit Langote <Langote_Amit_f8@lab.ntt.co.jp> writes:
I wonder if ScalarArrayOpExpr is not really meant for multi-dimensional
arrays appearing on the right hand side? Because:
# select array[1] = any (array[array[1], array[2]]);ERROR: operator does not exist: integer[] = integer
You are falling into the misimpression that a 2-D array is an array of
1-D arrays. It is not, even if the syntax makes it look like that.ScalarArrayOpExpr just iterates over the array elements without regard
to dimensionality; so the LHS must be of the element type.
Yeah, I can now see that.
Although, I wonder if there is any room for improvement here. Instead of
waiting for make_scalar_array_op() to emit the error as it does today,
would it be better if we error'd out earlier saying "ERROR: ANY/ALL
leftarg must be scalar, not array"? Attached a patch for that, if it's
worth going for at all.
Thanks,
Amit