Postgres Pro
Downloads
Home   >   mailing lists

Thread: Commutation of array SOME/ANY and ALL operators

Commutation of array SOME/ANY and ALL operators

From
Matthew Morrissette Vance
Date:
Assume the following table structure for my examples.
CREATE TABLE my_table (
    id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    int_array integer[],
    text_array text[]
);
INSERT INTO my_table (id, int_array, text_array) VALUES
  (1, '{5,10}', '{"1234%", "%5678"}'),
  (2, '{8,15}', '{"1234", "5678"}');

The `SOME/ANY` and `ALL` operators are helpful in determining if a given array element contains any/all values that match a provided expression.

The current operator works like:
SELECT * FROM my_table WHERE 5 = SOME(int_array);

This works great for the `=` operator and most other arithmetic operators as the commutation of the operator is either the same or has a commuted version of the operator (i.e. < >, >= <=, etc).

But for other operators like `LIKE` you can express:
SELECT * FROM my_table WHERE '12345789' LIKE ANY(text_array);

While this is helpful, that's the translation of:
find any value in the array that a provided value is `LIKE`

What if I instead what the translation of:
find if a provided value is like any element in the array.

i.e.:
SELECT * FROM my_table WHERE ANY(text_array) LIKE '1234%';

but you can't. 
Because the `ANY/SOME` and `ALL` operators don't support the commutation of the operator.

While this is technically possible using a custom defined operator that reverses the arguments order, that requires that you create a custom defined operator for every single type of operator you want to be able to use with `ANY/ALL`.  Any predicate function would require a custom operator defined or a custom version of the function that reverses the operators to be made.

There have been a number of issues raised around this:
https://stackoverflow.com/questions/55480064/how-to-search-in-array-with-like-operator

If instead, PostgreSQL could support the commutation of the `SOME/ANY` and `ALL` operators so that the `ANY(array)` could be on both sides of the provided operator, it would allow for this kind of searching natively.


Firstly, would a PR that enhanced PostgreSQL in this manner be accepted?
Secondly, could anyone provide me a few tips of where I should start looking at code to implement it?





Re: Commutation of array SOME/ANY and ALL operators

From
Tom Lane
Date:
Matthew Morrissette Vance <yinzara@gmail.com> writes:
> If instead, PostgreSQL could support the commutation of the `SOME/ANY` and
> `ALL` operators so that the `ANY(array)` could be on both sides of the
> provided operator, it would allow for this kind of searching natively.

> Firstly, would a PR that enhanced PostgreSQL in this manner be accepted?

My gut feeling is you'll run into insurmountable grammar-ambiguity
problems.  I might be wrong, but I have an idea that this has
already been tried and failed on that point.

Inventing commutator operators for LIKE etc could be a path of
much less resistance (unless the operator names get bikeshedded
to death).  Are there really that many that people need?
A quick query of pg_operator suggests that the LIKE/regex family
is the bulk of the problem for real-world cases.

            regards, tom lane

Re: Commutation of array SOME/ANY and ALL operators

From
Matthew Morrissette Vance
Date:
> Inventing commutator operators for LIKE etc could be a path of
> much less resistance (unless the operator names get bikeshedded
> to death).  Are there really that many that people need?
> A quick query of pg_operator suggests that the LIKE/regex family
> is the bulk of the problem for real-world cases.

Sadly, I was afraid that might be the case.  It would be so nice if it wasn't but if we have to go the path of least resistance.

We'd need a standard for the reversed version of a command that didn't intersect with any other command or SQL keyword.
We would also need a standard for the reversed version of operators that don't have a commutation that we want to support the opposite of.

The only commands/operators that I think are probably really wanted in that category would be:
* LIKE
* ILIKE
* ~ (regex match)
* ~* (regex case insensitive match)
* !~ (not regex match)
* !~* (not regex case insensitive match)

Proposed: (prefix the command/operator with ~ and put it before the ! if it's a "not" expression)
* EKIL (LIKE backwards) or RLIKE or CLIKE or ~LIKE
* EKILI (ILIKE backwards) or RILIKE or CILIKE or ~ILIKE
* ~~
* ~~* or ~*
* !~~
* !~~* or !~* or *~!

On Wed, Oct 23, 2024 at 6:57 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
Matthew Morrissette Vance <yinzara@gmail.com> writes:
> If instead, PostgreSQL could support the commutation of the `SOME/ANY` and
> `ALL` operators so that the `ANY(array)` could be on both sides of the
> provided operator, it would allow for this kind of searching natively.

> Firstly, would a PR that enhanced PostgreSQL in this manner be accepted?

My gut feeling is you'll run into insurmountable grammar-ambiguity
problems.  I might be wrong, but I have an idea that this has
already been tried and failed on that point.

Inventing commutator operators for LIKE etc could be a path of
much less resistance (unless the operator names get bikeshedded
to death).  Are there really that many that people need?
A quick query of pg_operator suggests that the LIKE/regex family
is the bulk of the problem for real-world cases.

                        regards, tom lane