Commutation of array SOME/ANY and ALL operators
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/34657669/find-rows-where-text-array-contains-value-similar-to-input
https://dba.stackexchange.com/questions/228235/match-string-pattern-to-any-array-element
https://dba.stackexchange.com/questions/117767/postgresql-pattern-match-against-array-elements
https://dba.stackexchange.com/questions/268884/postgres-having-ilike-any-wildcard-in-array-agg
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?
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
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:
Show quoted text
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