Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

Array comparision #6806

Open
izveigor opened this issue Jun 30, 2023 · 0 comments
Open

Array comparision #6806

izveigor opened this issue Jun 30, 2023 · 0 comments
Labels
enhancement New feature or request

Comments

@izveigor
Copy link
Contributor

Is your feature request related to a problem or challenge?

No response

Describe the solution you'd like

Between arrays:

postgres=# select * from nested_arrays;
         a         |        b         | id 
-------------------+------------------+----
 {{1,2},{6,8}}     | {{3,4},{2,4}}    |  1
 {{5,7},{1,10}}    | {{1,6},{2,2}}    |  2
 {{NULL,7},{1,10}} | {{1,NULL},{2,2}} |  3
 {{5,6},{7,8}}     | {{1,2},{3,4}}    |  4
 {{1,2},{3,4}}     | {{5,6},{7,8}}    |  5
 {{1,2},{3,4}}     | {{1,2},{3,4}}    |  6
 {{7,8},{NULL,4}}  | {{7,8},{NULL,4}} |  7
                   | {{1,2},{3,4}}    |  8
(8 rows)

postgres=# select * from arrays;
    a     |    b     |    c     |    d     | id 
----------+----------+----------+----------+----
 {1,2}    | {6,8}    | {3,4}    | {2,4}    |  1
 {5,7}    | {1,10}   | {1,6}    | {2,2}    |  2
 {NULL,7} | {1,10}   | {1,NULL} | {2,2}    |  3
 {5,6}    | {7,8}    | {1,2}    | {3,4}    |  4
 {1,2}    | {3,4}    | {5,6}    | {7,8}    |  5
 {1,2}    | {3,4}    | {1,2}    | {3,4}    |  6
 {7,8}    | {NULL,4} | {7,8}    | {NULL,4} |  7
          |          | {1,2}    | {3,4}    |  8
(8 rows)

postgres=# select * from ints;
 a | b  | c | d | id 
---+----+---+---+----
 6 |  8 | 2 | 4 |  1
 1 | 10 | 2 | 2 |  2
 1 | 10 | 2 | 2 |  3
 7 |  8 | 3 | 4 |  4
 3 |  4 | 7 | 8 |  5
 3 |  4 | 3 | 4 |  6
   |  4 |   | 4 |  7
   |    | 3 | 4 |  8
(8 rows)

postgres=# select arrays.b < arrays.d as "b1 < d1", ints.a < ints.c as "a2 < c2", ints.b < ints.d as "b2 < d2"
, arrays.b as b1, arrays.d as d1, ints.a as a2, ints.b as b2, ints.c as c2, ints.d as d2 from ints inner join 
arrays on arrays.id = ints.id;
 b1 < d1 | a2 < c2 | b2 < d2 |    b1    |    d1    | a2 | b2 | c2 | d2 
---------+---------+---------+----------+----------+----+----+----+----
 f       | f       | f       | {6,8}    | {2,4}    |  6 |  8 |  2 |  4
 t       | t       | f       | {1,10}   | {2,2}    |  1 | 10 |  2 |  2
 t       | t       | f       | {1,10}   | {2,2}    |  1 | 10 |  2 |  2
 f       | f       | f       | {7,8}    | {3,4}    |  7 |  8 |  3 |  4
 t       | t       | t       | {3,4}    | {7,8}    |  3 |  4 |  7 |  8
 f       | f       | f       | {3,4}    | {3,4}    |  3 |  4 |  3 |  4
 f       |         | f       | {NULL,4} | {NULL,4} |    |  4 |    |  4
         |         |         |          | {3,4}    |    |    |  3 |  4
(8 rows)

postgres=# select arrays.b <= arrays.d as "b1 <= d1", ints.a <= ints.c as "a2 <= c2", ints.b <= ints.d as "b2 
<= d2", arrays.b as b1, arrays.d as d1, ints.a as a2, ints.b as b2, ints.c as c2, ints.d as d2 from ints inner
 join arrays on arrays.id = ints.id;
 b1 <= d1 | a2 <= c2 |  b2  +|    b1    |    d1    | a2 | b2 | c2 | d2 
          |          | <= d2 |          |          |    |    |    |    
----------+----------+-------+----------+----------+----+----+----+----
 f        | f        | f     | {6,8}    | {2,4}    |  6 |  8 |  2 |  4
 t        | t        | f     | {1,10}   | {2,2}    |  1 | 10 |  2 |  2
 t        | t        | f     | {1,10}   | {2,2}    |  1 | 10 |  2 |  2
 f        | f        | f     | {7,8}    | {3,4}    |  7 |  8 |  3 |  4
 t        | t        | t     | {3,4}    | {7,8}    |  3 |  4 |  7 |  8
 t        | t        | t     | {3,4}    | {3,4}    |  3 |  4 |  3 |  4
 t        |          | t     | {NULL,4} | {NULL,4} |    |  4 |    |  4
          |          |       |          | {3,4}    |    |    |  3 |  4
(8 rows)

postgres=# select arrays.b = arrays.d as "b1 = d1", ints.a = ints.c as "a2 = c2", ints.b = ints.d as "b2 = d2"
, arrays.b as b1, arrays.d as d1, ints.a as a2, ints.b as b2, ints.c as c2, ints.d as d2 from ints inner join 
arrays on arrays.id = ints.id;
 b1 = d1 | a2 = c2 | b2 = d2 |    b1    |    d1    | a2 | b2 | c2 | d2 
---------+---------+---------+----------+----------+----+----+----+----
 f       | f       | f       | {6,8}    | {2,4}    |  6 |  8 |  2 |  4
 f       | f       | f       | {1,10}   | {2,2}    |  1 | 10 |  2 |  2
 f       | f       | f       | {1,10}   | {2,2}    |  1 | 10 |  2 |  2
 f       | f       | f       | {7,8}    | {3,4}    |  7 |  8 |  3 |  4
 f       | f       | f       | {3,4}    | {7,8}    |  3 |  4 |  7 |  8
 t       | t       | t       | {3,4}    | {3,4}    |  3 |  4 |  3 |  4
 t       |         | t       | {NULL,4} | {NULL,4} |    |  4 |    |  4
         |         |         |          | {3,4}    |    |    |  3 |  4
(8 rows)

postgres=# select arrays.b <> arrays.d as "b1 <> d1", ints.a <> ints.c as "a2 <> c2", ints.b <> ints.d as "b2 
<> d2", arrays.b as b1, arrays.d as d1, ints.a as a2, ints.b as b2, ints.c as c2, ints.d as d2 from ints inner
 join arrays on arrays.id = ints.id;
 b1 <> d1 | a2 <> c2 |  b2  +|    b1    |    d1    | a2 | b2 | c2 | d2 
          |          | <> d2 |          |          |    |    |    |    
----------+----------+-------+----------+----------+----+----+----+----
 t        | t        | t     | {6,8}    | {2,4}    |  6 |  8 |  2 |  4
 t        | t        | t     | {1,10}   | {2,2}    |  1 | 10 |  2 |  2
 t        | t        | t     | {1,10}   | {2,2}    |  1 | 10 |  2 |  2
 t        | t        | t     | {7,8}    | {3,4}    |  7 |  8 |  3 |  4
 t        | t        | t     | {3,4}    | {7,8}    |  3 |  4 |  7 |  8
 f        | f        | f     | {3,4}    | {3,4}    |  3 |  4 |  3 |  4
 f        |          | f     | {NULL,4} | {NULL,4} |    |  4 |    |  4
          |          |       |          | {3,4}    |    |    |  3 |  4
(8 rows)


postgres=# select arrays.b > arrays.d as "b1 > d1", ints.a > ints.c as "a2 > c2", ints.b > ints.d as "b2 > d2"
, arrays.b as b1, arrays.d as d1, ints.a as a2, ints.b as b2, ints.c as c2, ints.d as d2 from ints inner join 
arrays on arrays.id = ints.id;
 b1 > d1 | a2 > c2 | b2 > d2 |    b1    |    d1    | a2 | b2 | c2 | d2 
---------+---------+---------+----------+----------+----+----+----+----
 t       | t       | t       | {6,8}    | {2,4}    |  6 |  8 |  2 |  4
 f       | f       | t       | {1,10}   | {2,2}    |  1 | 10 |  2 |  2
 f       | f       | t       | {1,10}   | {2,2}    |  1 | 10 |  2 |  2
 t       | t       | t       | {7,8}    | {3,4}    |  7 |  8 |  3 |  4
 f       | f       | f       | {3,4}    | {7,8}    |  3 |  4 |  7 |  8
 f       | f       | f       | {3,4}    | {3,4}    |  3 |  4 |  3 |  4
 f       |         | f       | {NULL,4} | {NULL,4} |    |  4 |    |  4
         |         |         |          | {3,4}    |    |    |  3 |  4
(8 rows)

postgres=# select arrays.b >= arrays.d as "b1 >= d1", ints.a >= ints.c as "a2 >= c2", ints.b >= ints.d as "b2 
>= d2", arrays.b as b1, arrays.d as d1, ints.a as a2, ints.b as b2, ints.c as c2, ints.d as d2 from ints inner
 join arrays on arrays.id = ints.id;
 b1 >= d1 | a2 >= c2 |  b2  +|    b1    |    d1    | a2 | b2 | c2 | d2 
          |          | >= d2 |          |          |    |    |    |    
----------+----------+-------+----------+----------+----+----+----+----
 t        | t        | t     | {6,8}    | {2,4}    |  6 |  8 |  2 |  4
 f        | f        | t     | {1,10}   | {2,2}    |  1 | 10 |  2 |  2
 f        | f        | t     | {1,10}   | {2,2}    |  1 | 10 |  2 |  2
 t        | t        | t     | {7,8}    | {3,4}    |  7 |  8 |  3 |  4
 f        | f        | f     | {3,4}    | {7,8}    |  3 |  4 |  7 |  8
 t        | t        | t     | {3,4}    | {3,4}    |  3 |  4 |  3 |  4
 t        |          | t     | {NULL,4} | {NULL,4} |    |  4 |    |  4
          |          |       |          | {3,4}    |    |    |  3 |  4
(8 rows)

postgres=# select nested_arrays.a < nested_arrays.b as "a1 < b1", arrays.a < arrays.c as "a2 < c2", arrays.b < arrays.d as "b2 < d2", nested_arrays.a as a1, nested_arrays.b as b1, arrays.a as a2, arrays.b as b2, arrays.c as c2, arrays.d as d2 from arrays inner join nested_arrays 
on arrays.id = nested_arrays.id;
 a1 < b1 | a2 < c2 | b2 < d2 |        a1         |        b1        |    a2    |    b2    |    c2    |    d2    
---------+---------+---------+-------------------+------------------+----------+----------+----------+----------
 t       | t       | f       | {{1,2},{6,8}}     | {{3,4},{2,4}}    | {1,2}    | {6,8}    | {3,4}    | {2,4}
 f       | f       | t       | {{5,7},{1,10}}    | {{1,6},{2,2}}    | {5,7}    | {1,10}   | {1,6}    | {2,2}
 f       | f       | t       | {{NULL,7},{1,10}} | {{1,NULL},{2,2}} | {NULL,7} | {1,10}   | {1,NULL} | {2,2}
 f       | f       | f       | {{5,6},{7,8}}     | {{1,2},{3,4}}    | {5,6}    | {7,8}    | {1,2}    | {3,4}
 t       | t       | t       | {{1,2},{3,4}}     | {{5,6},{7,8}}    | {1,2}    | {3,4}    | {5,6}    | {7,8}
 f       | f       | f       | {{1,2},{3,4}}     | {{1,2},{3,4}}    | {1,2}    | {3,4}    | {1,2}    | {3,4}
 f       | f       | f       | {{7,8},{NULL,4}}  | {{7,8},{NULL,4}} | {7,8}    | {NULL,4} | {7,8}    | {NULL,4}
         |         |         |                   | {{1,2},{3,4}}    |          |          | {1,2}    | {3,4}
(8 rows)

postgres=# select nested_arrays.a <= nested_arrays.b as "a1 <= b1", arrays.a <= arrays.c as "a2 <= c2", arrays.b <= arrays.d as "b2 <= d2", 
nested_arrays.a as a1, nested_arrays.b as b1, arrays.a as a2, arrays.b as b2, arrays.c as c2, arrays.d as d2 from arrays inner join nested_arrays on arrays.id = nested
_arrays.id;
 a1 <= b1 | a2 <= c2 | b2 <= d2 |        a1         |        b1        |    a2    |    b2    |    c2    |    d2    
----------+----------+----------+-------------------+------------------+----------+----------+----------+----------
 t        | t        | f        | {{1,2},{6,8}}     | {{3,4},{2,4}}    | {1,2}    | {6,8}    | {3,4}    | {2,4}
 f        | f        | t        | {{5,7},{1,10}}    | {{1,6},{2,2}}    | {5,7}    | {1,10}   | {1,6}    | {2,2}
 f        | f        | t        | {{NULL,7},{1,10}} | {{1,NULL},{2,2}} | {NULL,7} | {1,10}   | {1,NULL} | {2,2}
 f        | f        | f        | {{5,6},{7,8}}     | {{1,2},{3,4}}    | {5,6}    | {7,8}    | {1,2}    | {3,4}
 t        | t        | t        | {{1,2},{3,4}}     | {{5,6},{7,8}}    | {1,2}    | {3,4}    | {5,6}    | {7,8}
 t        | t        | t        | {{1,2},{3,4}}     | {{1,2},{3,4}}    | {1,2}    | {3,4}    | {1,2}    | {3,4}
 t        | t        | t        | {{7,8},{NULL,4}}  | {{7,8},{NULL,4}} | {7,8}    | {NULL,4} | {7,8}    | {NULL,4}
          |          |          |                   | {{1,2},{3,4}}    |          |          | {1,2}    | {3,4}
(8 rows)

postgres=# select arrays.b IS DISTINCT FROM arrays.d as "b1 IS DISTINCT FROM d1", ints.a IS DISTINCT FROM ints.c as "a2 IS DISTINCT FROM c2", ints.b IS DISTINCT FROM i
nts.d as "b2 IS DISTINCT FROM d2", arrays.b as b1, arrays.d as d1, ints.a as a2, ints.b as b2, ints.c as c2, ints.d as d2 from ints inner
 join arrays on arrays.id = ints.id;
 b1 IS DISTINCT FROM d1 | a2 IS DISTINCT FROM c2 | b2 IS DISTINCT FROM d2 |    b1    |    d1    | a2 | b2 | c2 | d2 
------------------------+------------------------+------------------------+----------+----------+----+----+----+----
 t                      | t                      | t                      | {6,8}    | {2,4}    |  6 |  8 |  2 |  4
 t                      | t                      | t                      | {1,10}   | {2,2}    |  1 | 10 |  2 |  2
 t                      | t                      | t                      | {1,10}   | {2,2}    |  1 | 10 |  2 |  2
 t                      | t                      | t                      | {7,8}    | {3,4}    |  7 |  8 |  3 |  4
 t                      | t                      | t                      | {3,4}    | {7,8}    |  3 |  4 |  7 |  8
 f                      | f                      | f                      | {3,4}    | {3,4}    |  3 |  4 |  3 |  4
 f                      | f                      | f                      | {NULL,4} | {NULL,4} |    |  4 |    |  4
 t                      | t                      | t                      |          | {3,4}    |    |    |  3 |  4
(8 rows)

postgres=# select arrays.b IS NOT DISTINCT FROM arrays.d as "b1 IS NOT DISTINCT FROM d1", ints.a IS NOT DISTINCT FROM ints.c as "a2 IS NOT DISTINCT FROM c2", ints.b IS
 NOT DISTINCT FROM ints.d as "b2 IS NOT DISTINCT FROM d2", arrays.b as b1, arrays.d as d1, ints.a as a2, ints.b as b2, ints.c as c2, ints.d as d2 from ints inner
 join arrays on arrays.id = ints.id;
 b1 IS NOT DISTINCT FROM d1 | a2 IS NOT DISTINCT FROM c2 | b2 IS NOT DISTINCT FROM d2 |    b1    |    d1    | a2 | b2 | c2 | d2 
----------------------------+----------------------------+----------------------------+----------+----------+----+----+----+----
 f                          | f                          | f                          | {6,8}    | {2,4}    |  6 |  8 |  2 |  4
 f                          | f                          | f                          | {1,10}   | {2,2}    |  1 | 10 |  2 |  2
 f                          | f                          | f                          | {1,10}   | {2,2}    |  1 | 10 |  2 |  2
 f                          | f                          | f                          | {7,8}    | {3,4}    |  7 |  8 |  3 |  4
 f                          | f                          | f                          | {3,4}    | {7,8}    |  3 |  4 |  7 |  8
 t                          | t                          | t                          | {3,4}    | {3,4}    |  3 |  4 |  3 |  4
 t                          | t                          | t                          | {NULL,4} | {NULL,4} |    |  4 |    |  4
 f                          | f                          | f                          |          | {3,4}    |    |    |  3 |  4
(8 rows)

postgres=# select nested_arrays.a = nested_arrays.b as "a1 = b1", arrays.a = arrays.c as "a2 = c2", arrays.b = arrays.d as "b2 = d2", nested_arrays.a as a1, nested_arr
ays.b as b1, arrays.a as a2, arrays.b as b2, arrays.c as c2, arrays.d as d2 from arrays inner join nested_arrays 
on arrays.id = nested_arrays.id;
 a1 = b1 | a2 = c2 | b2 = d2 |        a1         |        b1        |    a2    |    b2    |    c2    |    d2    
---------+---------+---------+-------------------+------------------+----------+----------+----------+----------
 f       | f       | f       | {{1,2},{6,8}}     | {{3,4},{2,4}}    | {1,2}    | {6,8}    | {3,4}    | {2,4}
 f       | f       | f       | {{5,7},{1,10}}    | {{1,6},{2,2}}    | {5,7}    | {1,10}   | {1,6}    | {2,2}
 f       | f       | f       | {{NULL,7},{1,10}} | {{1,NULL},{2,2}} | {NULL,7} | {1,10}   | {1,NULL} | {2,2}
 f       | f       | f       | {{5,6},{7,8}}     | {{1,2},{3,4}}    | {5,6}    | {7,8}    | {1,2}    | {3,4}
 f       | f       | f       | {{1,2},{3,4}}     | {{5,6},{7,8}}    | {1,2}    | {3,4}    | {5,6}    | {7,8}
 t       | t       | t       | {{1,2},{3,4}}     | {{1,2},{3,4}}    | {1,2}    | {3,4}    | {1,2}    | {3,4}
 t       | t       | t       | {{7,8},{NULL,4}}  | {{7,8},{NULL,4}} | {7,8}    | {NULL,4} | {7,8}    | {NULL,4}
         |         |         |                   | {{1,2},{3,4}}    |          |          | {1,2}    | {3,4}
(8 rows)

postgres=# select nested_arrays.a <> nested_arrays.b as "a1 <> b1", arrays.a <> arrays.c as "a2 <> c2", arrays.b <> arrays.d as "b2 <> d2", 
nested_arrays.a as a1, nested_arrays.b as b1, arrays.a as a2, arrays.b as b2, arrays.c as c2, arrays.d as d2 from arrays inner join nested_arrays on arrays.id = nested
_arrays.id;
 a1 <> b1 | a2 <> c2 | b2 <> d2 |        a1         |        b1        |    a2    |    b2    |    c2    |    d2    
----------+----------+----------+-------------------+------------------+----------+----------+----------+----------
 t        | t        | t        | {{1,2},{6,8}}     | {{3,4},{2,4}}    | {1,2}    | {6,8}    | {3,4}    | {2,4}
 t        | t        | t        | {{5,7},{1,10}}    | {{1,6},{2,2}}    | {5,7}    | {1,10}   | {1,6}    | {2,2}
 t        | t        | t        | {{NULL,7},{1,10}} | {{1,NULL},{2,2}} | {NULL,7} | {1,10}   | {1,NULL} | {2,2}
 t        | t        | t        | {{5,6},{7,8}}     | {{1,2},{3,4}}    | {5,6}    | {7,8}    | {1,2}    | {3,4}
 t        | t        | t        | {{1,2},{3,4}}     | {{5,6},{7,8}}    | {1,2}    | {3,4}    | {5,6}    | {7,8}
 f        | f        | f        | {{1,2},{3,4}}     | {{1,2},{3,4}}    | {1,2}    | {3,4}    | {1,2}    | {3,4}
 f        | f        | f        | {{7,8},{NULL,4}}  | {{7,8},{NULL,4}} | {7,8}    | {NULL,4} | {7,8}    | {NULL,4}
          |          |          |                   | {{1,2},{3,4}}    |          |          | {1,2}    | {3,4}
(8 rows)

postgres=# select nested_arrays.a > nested_arrays.b as "a1 > b1", arrays.a > arrays.c as "a2 > c2", arrays.b > arrays.d as "b2 > d2", nested_arrays.a as a1, nested_arr
ays.b as b1, arrays.a as a2, arrays.b as b2, arrays.c as c2, arrays.d as d2 from arrays inner join nested_arrays 
on arrays.id = nested_arrays.id;
 a1 > b1 | a2 > c2 | b2 > d2 |        a1         |        b1        |    a2    |    b2    |    c2    |    d2    
---------+---------+---------+-------------------+------------------+----------+----------+----------+----------
 f       | f       | t       | {{1,2},{6,8}}     | {{3,4},{2,4}}    | {1,2}    | {6,8}    | {3,4}    | {2,4}
 t       | t       | f       | {{5,7},{1,10}}    | {{1,6},{2,2}}    | {5,7}    | {1,10}   | {1,6}    | {2,2}
 t       | t       | f       | {{NULL,7},{1,10}} | {{1,NULL},{2,2}} | {NULL,7} | {1,10}   | {1,NULL} | {2,2}
 t       | t       | t       | {{5,6},{7,8}}     | {{1,2},{3,4}}    | {5,6}    | {7,8}    | {1,2}    | {3,4}
 f       | f       | f       | {{1,2},{3,4}}     | {{5,6},{7,8}}    | {1,2}    | {3,4}    | {5,6}    | {7,8}
 f       | f       | f       | {{1,2},{3,4}}     | {{1,2},{3,4}}    | {1,2}    | {3,4}    | {1,2}    | {3,4}
 f       | f       | f       | {{7,8},{NULL,4}}  | {{7,8},{NULL,4}} | {7,8}    | {NULL,4} | {7,8}    | {NULL,4}
         |         |         |                   | {{1,2},{3,4}}    |          |          | {1,2}    | {3,4}
(8 rows)

postgres=# select nested_arrays.a >= nested_arrays.b as "a1 >= b1", arrays.a >= arrays.c as "a2 >= c2", arrays.b >= arrays.d as "b2 >= d2", 
nested_arrays.a as a1, nested_arrays.b as b1, arrays.a as a2, arrays.b as b2, arrays.c as c2, arrays.d as d2 from arrays inner join nested_arrays on arrays.id = nested
_arrays.id;
 a1 >= b1 | a2 >= c2 | b2 >= d2 |        a1         |        b1        |    a2    |    b2    |    c2    |    d2    
----------+----------+----------+-------------------+------------------+----------+----------+----------+----------
 f        | f        | t        | {{1,2},{6,8}}     | {{3,4},{2,4}}    | {1,2}    | {6,8}    | {3,4}    | {2,4}
 t        | t        | f        | {{5,7},{1,10}}    | {{1,6},{2,2}}    | {5,7}    | {1,10}   | {1,6}    | {2,2}
 t        | t        | f        | {{NULL,7},{1,10}} | {{1,NULL},{2,2}} | {NULL,7} | {1,10}   | {1,NULL} | {2,2}
 t        | t        | t        | {{5,6},{7,8}}     | {{1,2},{3,4}}    | {5,6}    | {7,8}    | {1,2}    | {3,4}
 f        | f        | f        | {{1,2},{3,4}}     | {{5,6},{7,8}}    | {1,2}    | {3,4}    | {5,6}    | {7,8}
 t        | t        | t        | {{1,2},{3,4}}     | {{1,2},{3,4}}    | {1,2}    | {3,4}    | {1,2}    | {3,4}
 t        | t        | t        | {{7,8},{NULL,4}}  | {{7,8},{NULL,4}} | {7,8}    | {NULL,4} | {7,8}    | {NULL,4}
          |          |          |                   | {{1,2},{3,4}}    |          |          | {1,2}    | {3,4}
(8 rows)

postgres=# select nested_arrays.a IS DISTINCT FROM nested_arrays.b as "a1 IS DISTINCT FROM b1", arrays.a IS DISTINCT FROM arrays.c as "a2 IS DISTINCT FROM c2", arrays.
b IS DISTINCT FROM arrays.d as "b2 IS DISTINCT FROM d2",                                                                                                               
nested_arrays.a as a1, nested_arrays.b as b1, arrays.a as a2, arrays.b as b2, arrays.c as c2, arrays.d as d2 from arrays inner join nested_arrays on arrays.id = nested
_arrays.id;
 a1 IS DISTINCT FROM b1 | a2 IS DISTINCT FROM c2 | b2 IS DISTINCT FROM d2 |        a1         |        b1        |    a2    |    b2    |    c2    |    d2    
------------------------+------------------------+------------------------+-------------------+------------------+----------+----------+----------+----------
 t                      | t                      | t                      | {{1,2},{6,8}}     | {{3,4},{2,4}}    | {1,2}    | {6,8}    | {3,4}    | {2,4}
 t                      | t                      | t                      | {{5,7},{1,10}}    | {{1,6},{2,2}}    | {5,7}    | {1,10}   | {1,6}    | {2,2}
 t                      | t                      | t                      | {{NULL,7},{1,10}} | {{1,NULL},{2,2}} | {NULL,7} | {1,10}   | {1,NULL} | {2,2}
 t                      | t                      | t                      | {{5,6},{7,8}}     | {{1,2},{3,4}}    | {5,6}    | {7,8}    | {1,2}    | {3,4}
 t                      | t                      | t                      | {{1,2},{3,4}}     | {{5,6},{7,8}}    | {1,2}    | {3,4}    | {5,6}    | {7,8}
 f                      | f                      | f                      | {{1,2},{3,4}}     | {{1,2},{3,4}}    | {1,2}    | {3,4}    | {1,2}    | {3,4}
 f                      | f                      | f                      | {{7,8},{NULL,4}}  | {{7,8},{NULL,4}} | {7,8}    | {NULL,4} | {7,8}    | {NULL,4}
 t                      | t                      | t                      |                   | {{1,2},{3,4}}    |          |          | {1,2}    | {3,4}
(8 rows

postgres=# select nested_arrays.a IS NOT DISTINCT FROM nested_arrays.b as "a1 IS NOT DISTINCT FROM b1", arrays.a IS NOT DISTINCT FROM arrays.c as "a2 IS NOT DISTINCT FROM c2", arrays.b IS NOT DISTINCT FROM arrays.d as "b2 IS NOT DISTINCT FROM d2", 
nested_arrays.a as a1, nested_arrays.b as b1, arrays.a as a2, arrays.b as b2, arrays.c as c2, arrays.d as d2 from arrays inner join nested_arrays on arrays.id = nested_arrays.id;
 a1 IS NOT DISTINCT FROM b1 | a2 IS NOT DISTINCT FROM c2 | b2 IS NOT DISTINCT FROM d2 |        a1         |        b1        |    a2    |    b2    |    c2    |    d2    
----------------------------+----------------------------+----------------------------+-------------------+------------------+----------+----------+----------+----------
 f                          | f                          | f                          | {{1,2},{6,8}}     | {{3,4},{2,4}}    | {1,2}    | {6,8}    | {3,4}    | {2,4}
 f                          | f                          | f                          | {{5,7},{1,10}}    | {{1,6},{2,2}}    | {5,7}    | {1,10}   | {1,6}    | {2,2}
 f                          | f                          | f                          | {{NULL,7},{1,10}} | {{1,NULL},{2,2}} | {NULL,7} | {1,10}   | {1,NULL} | {2,2}
 f                          | f                          | f                          | {{5,6},{7,8}}     | {{1,2},{3,4}}    | {5,6}    | {7,8}    | {1,2}    | {3,4}
 f                          | f                          | f                          | {{1,2},{3,4}}     | {{5,6},{7,8}}    | {1,2}    | {3,4}    | {5,6}    | {7,8}
 t                          | t                          | t                          | {{1,2},{3,4}}     | {{1,2},{3,4}}    | {1,2}    | {3,4}    | {1,2}    | {3,4}
 t                          | t                          | t                          | {{7,8},{NULL,4}}  | {{7,8},{NULL,4}} | {7,8}    | {NULL,4} | {7,8}    | {NULL,4}
 f                          | f                          | f                          |                   | {{1,2},{3,4}}    |          |          | {1,2}    | {3,4}
(8 rows)

postgres=# select * from long_nested_arrays;
             a              |             b             | id 
----------------------------+---------------------------+----
 {{1,2},{6,8},{4,7}}        | {{3,4},{2,4},{3,5}}       |  1
 {{5,7},{1,10},{5,6}}       | {{1,6},{2,2},{4,7}}       |  2
 {{NULL,7},{1,10},{NULL,6}} | {{1,NULL},{2,2},{4,NULL}} |  3
 {{5,6},{7,8},{9,10}}       | {{1,2},{3,4},{5,6}}       |  4
 {{1,2},{3,4},{5,6}}        | {{5,6},{7,8},{9,10}}      |  5
 {{1,2},{3,4},{5,6}}        | {{1,2},{3,4},{5,6}}       |  6
 {{7,8},{NULL,4},{5,NULL}}  | {{7,8},{NULL,4},{5,NULL}} |  7
                            | {{1,2},{3,4},{5,6}}       |  8
(8 rows)

postgres=# select * from long_arrays;
    a     |    b     |    c     |    d     |    e     |    f     | id 
----------+----------+----------+----------+----------+----------+----
 {1,2}    | {6,8}    | {4,7}    | {3,4}    | {2,4}    | {3,5}    |  1
 {5,7}    | {1,10}   | {5,6}    | {1,6}    | {2,2}    | {4,7}    |  2
 {NULL,7} | {1,10}   | {NULL,6} | {1,NULL} | {2,2}    | {4,NULL} |  3
 {5,6}    | {7,8}    | {9,10}   | {1,2}    | {3,4}    | {5,6}    |  4
 {1,2}    | {3,4}    | {5,6}    | {5,6}    | {7,8}    | {9,10}   |  5
 {1,2}    | {3,4}    | {5,6}    | {1,2}    | {3,4}    | {5,6}    |  6
 {7,8}    | {NULL,4} | {5,NULL} | {7,8}    | {NULL,4} | {5,NULL} |  7
          |          |          | {1,2}    | {3,4}    | {5,6}    |  8
(8 rows)

postgres=# select long_nested_arrays.a <= long_nested_arrays.b as "a1 <= b1", long_arrays.a <= long_arrays.d as "a2 <= d2", long_arrays.b <= long_arrays.e as "b2 <= e2", long_arrays.c <= long_arrays.f as "c2 <= f2", long_nested_arrays.a as a1, long_nested_arrays.b as b1, long_arrays.a as a2, long_arrays.b as b2, long_arrays.c as c2, long_arrays.d as d2, long_arrays.e as e2, long_arrays.f as f2 from long_arrays inner join long_nested_arrays on long_arrays.id = long_nested_arrays.id;
 a1 <= b1 | a2 <= d2 | b2 <= e2 | c2 <= f2 |             a1             |            b1             |    a2    |    b2    |    c2    |    d2    |    e2    |    f2    
----------+----------+----------+----------+----------------------------+---------------------------+----------+----------+----------+----------+----------+----------
 t        | t        | f        | f        | {{1,2},{6,8},{4,7}}        | {{3,4},{2,4},{3,5}}       | {1,2}    | {6,8}    | {4,7}    | {3,4}    | {2,4}    | {3,5}
 f        | f        | t        | f        | {{5,7},{1,10},{5,6}}       | {{1,6},{2,2},{4,7}}       | {5,7}    | {1,10}   | {5,6}    | {1,6}    | {2,2}    | {4,7}
 f        | f        | t        | f        | {{NULL,7},{1,10},{NULL,6}} | {{1,NULL},{2,2},{4,NULL}} | {NULL,7} | {1,10}   | {NULL,6} | {1,NULL} | {2,2}    | {4,NULL}
 f        | f        | f        | f        | {{5,6},{7,8},{9,10}}       | {{1,2},{3,4},{5,6}}       | {5,6}    | {7,8}    | {9,10}   | {1,2}    | {3,4}    | {5,6}
 t        | t        | t        | t        | {{1,2},{3,4},{5,6}}        | {{5,6},{7,8},{9,10}}      | {1,2}    | {3,4}    | {5,6}    | {5,6}    | {7,8}    | {9,10}
 t        | t        | t        | t        | {{1,2},{3,4},{5,6}}        | {{1,2},{3,4},{5,6}}       | {1,2}    | {3,4}    | {5,6}    | {1,2}    | {3,4}    | {5,6}
 t        | t        | t        | t        | {{7,8},{NULL,4},{5,NULL}}  | {{7,8},{NULL,4},{5,NULL}} | {7,8}    | {NULL,4} | {5,NULL} | {7,8}    | {NULL,4} | {5,NULL}
          |          |          |          |                            | {{1,2},{3,4},{5,6}}       |          |          |          | {1,2}    | {3,4}    | {5,6}
(8 rows)

postgres=# select long_nested_arrays.a < long_nested_arrays.b as "a1 < b1", long_arrays.a < long_arrays.d as "a2 < d2", long_arrays.b < long_arrays.e as "b2 < e2", long_arrays.c < long_arrays.f as "c2 < f2", long_nested_arrays.a as a1, long_nested_arrays.b as b1, long_arrays.a as a2, long_arrays.b as b2, long_arrays.c as c2, long_arrays.d as d2, long_arrays.e as e2, long_arrays.f as f2 from long_arrays inner join long_nested_arrays on long_arrays.id = long_nested_arrays.id;
 a1 < b1 | a2 < d2 | b2 < e2 | c2 < f2 |             a1             |            b1             |    a2    |    b2    |    c2    |    d2    |    e2    |    f2    
---------+---------+---------+---------+----------------------------+---------------------------+----------+----------+----------+----------+----------+----------
 t       | t       | f       | f       | {{1,2},{6,8},{4,7}}        | {{3,4},{2,4},{3,5}}       | {1,2}    | {6,8}    | {4,7}    | {3,4}    | {2,4}    | {3,5}
 f       | f       | t       | f       | {{5,7},{1,10},{5,6}}       | {{1,6},{2,2},{4,7}}       | {5,7}    | {1,10}   | {5,6}    | {1,6}    | {2,2}    | {4,7}
 f       | f       | t       | f       | {{NULL,7},{1,10},{NULL,6}} | {{1,NULL},{2,2},{4,NULL}} | {NULL,7} | {1,10}   | {NULL,6} | {1,NULL} | {2,2}    | {4,NULL}
 f       | f       | f       | f       | {{5,6},{7,8},{9,10}}       | {{1,2},{3,4},{5,6}}       | {5,6}    | {7,8}    | {9,10}   | {1,2}    | {3,4}    | {5,6}
 t       | t       | t       | t       | {{1,2},{3,4},{5,6}}        | {{5,6},{7,8},{9,10}}      | {1,2}    | {3,4}    | {5,6}    | {5,6}    | {7,8}    | {9,10}
 f       | f       | f       | f       | {{1,2},{3,4},{5,6}}        | {{1,2},{3,4},{5,6}}       | {1,2}    | {3,4}    | {5,6}    | {1,2}    | {3,4}    | {5,6}
 f       | f       | f       | f       | {{7,8},{NULL,4},{5,NULL}}  | {{7,8},{NULL,4},{5,NULL}} | {7,8}    | {NULL,4} | {5,NULL} | {7,8}    | {NULL,4} | {5,NULL}
         |         |         |         |                            | {{1,2},{3,4},{5,6}}       |          |          |          | {1,2}    | {3,4}    | {5,6}
(8 rows)

postgres=# select long_nested_arrays.a = long_nested_arrays.b as "a1 = b1", long_arrays.a = long_arrays.d as "a2 = d2", long_arrays.b = long_arrays.e as "b2 = e2", long_arrays.c = long_arrays.f as "c2 = f2", long_nested_arrays.a as a1, long_nested_arrays.b as b1, long_arrays.a as a2, long_arrays.b as b2, long_arrays.c as c2, long_ar
rays.d as d2, long_arrays.e as e2, long_arrays.f as f2 from long_arrays inner join long_nested_arrays on long_arrays.id = long_nested_arrays.id;
 a1 = b1 | a2 = d2 | b2 = e2 | c2 = f2 |             a1             |            b1             |    a2    |    b2    |    c2    |    d2    |    e2    |    f2    
---------+---------+---------+---------+----------------------------+---------------------------+----------+----------+----------+----------+----------+----------
 f       | f       | f       | f       | {{1,2},{6,8},{4,7}}        | {{3,4},{2,4},{3,5}}       | {1,2}    | {6,8}    | {4,7}    | {3,4}    | {2,4}    | {3,5}
 f       | f       | f       | f       | {{5,7},{1,10},{5,6}}       | {{1,6},{2,2},{4,7}}       | {5,7}    | {1,10}   | {5,6}    | {1,6}    | {2,2}    | {4,7}
 f       | f       | f       | f       | {{NULL,7},{1,10},{NULL,6}} | {{1,NULL},{2,2},{4,NULL}} | {NULL,7} | {1,10}   | {NULL,6} | {1,NULL} | {2,2}    | {4,NULL}
 f       | f       | f       | f       | {{5,6},{7,8},{9,10}}       | {{1,2},{3,4},{5,6}}       | {5,6}    | {7,8}    | {9,10}   | {1,2}    | {3,4}    | {5,6}
 f       | f       | f       | f       | {{1,2},{3,4},{5,6}}        | {{5,6},{7,8},{9,10}}      | {1,2}    | {3,4}    | {5,6}    | {5,6}    | {7,8}    | {9,10}
 t       | t       | t       | t       | {{1,2},{3,4},{5,6}}        | {{1,2},{3,4},{5,6}}       | {1,2}    | {3,4}    | {5,6}    | {1,2}    | {3,4}    | {5,6}
 t       | t       | t       | t       | {{7,8},{NULL,4},{5,NULL}}  | {{7,8},{NULL,4},{5,NULL}} | {7,8}    | {NULL,4} | {5,NULL} | {7,8}    | {NULL,4} | {5,NULL}
         |         |         |         |                            | {{1,2},{3,4},{5,6}}       |          |          |          | {1,2}    | {3,4}    | {5,6}
(8 rows)

postgres=# select long_nested_arrays.a <> long_nested_arrays.b as "a1 <> b1", long_arrays.a <> long_arrays.d as "a2 <> d2", long_arrays.b <> long_arrays.e as "b2 <> e2", long_arrays.c <> long_arrays.f as "c2 <> f2", long_nested_arrays.a as a1, long_nested_arrays.b as b1, long_arrays.a as a2, long_arrays.b as b2, long_arrays.c as c2,
 long_arrays.d as d2, long_arrays.e as e2, long_arrays.f as f2 from long_arrays inner join long_nested_arrays on long_arrays.id = long_nested_arrays.id;
 a1 <> b1 | a2 <> d2 | b2 <> e2 | c2 <> f2 |             a1             |            b1             |    a2    |    b2    |    c2    |    d2    |    e2    |    f2    
----------+----------+----------+----------+----------------------------+---------------------------+----------+----------+----------+----------+----------+----------
 t        | t        | t        | t        | {{1,2},{6,8},{4,7}}        | {{3,4},{2,4},{3,5}}       | {1,2}    | {6,8}    | {4,7}    | {3,4}    | {2,4}    | {3,5}
 t        | t        | t        | t        | {{5,7},{1,10},{5,6}}       | {{1,6},{2,2},{4,7}}       | {5,7}    | {1,10}   | {5,6}    | {1,6}    | {2,2}    | {4,7}
 t        | t        | t        | t        | {{NULL,7},{1,10},{NULL,6}} | {{1,NULL},{2,2},{4,NULL}} | {NULL,7} | {1,10}   | {NULL,6} | {1,NULL} | {2,2}    | {4,NULL}
 t        | t        | t        | t        | {{5,6},{7,8},{9,10}}       | {{1,2},{3,4},{5,6}}       | {5,6}    | {7,8}    | {9,10}   | {1,2}    | {3,4}    | {5,6}
 t        | t        | t        | t        | {{1,2},{3,4},{5,6}}        | {{5,6},{7,8},{9,10}}      | {1,2}    | {3,4}    | {5,6}    | {5,6}    | {7,8}    | {9,10}
 f        | f        | f        | f        | {{1,2},{3,4},{5,6}}        | {{1,2},{3,4},{5,6}}       | {1,2}    | {3,4}    | {5,6}    | {1,2}    | {3,4}    | {5,6}
 f        | f        | f        | f        | {{7,8},{NULL,4},{5,NULL}}  | {{7,8},{NULL,4},{5,NULL}} | {7,8}    | {NULL,4} | {5,NULL} | {7,8}    | {NULL,4} | {5,NULL}
          |          |          |          |                            | {{1,2},{3,4},{5,6}}       |          |          |          | {1,2}    | {3,4}    | {5,6}
(8 rows)

postgres=# select long_nested_arrays.a > long_nested_arrays.b as "a1 > b1", long_arrays.a > long_arrays.d as "a2 > d2", long_arrays.b > long_arrays.e as "b2 > e2", long_arrays.c > long_arrays.f as "c2 > f2", long_nested_arrays.a as a1, long_nested_arrays.b as b1, long_arrays.a as a2, long_arrays.b as b2, long_arrays.c as c2, long_ar
rays.d as d2, long_arrays.e as e2, long_arrays.f as f2 from long_arrays inner join long_nested_arrays on long_arrays.id = long_nested_arrays.id;
 a1 > b1 | a2 > d2 | b2 > e2 | c2 > f2 |             a1             |            b1             |    a2    |    b2    |    c2    |    d2    |    e2    |    f2    
---------+---------+---------+---------+----------------------------+---------------------------+----------+----------+----------+----------+----------+----------
 f       | f       | t       | t       | {{1,2},{6,8},{4,7}}        | {{3,4},{2,4},{3,5}}       | {1,2}    | {6,8}    | {4,7}    | {3,4}    | {2,4}    | {3,5}
 t       | t       | f       | t       | {{5,7},{1,10},{5,6}}       | {{1,6},{2,2},{4,7}}       | {5,7}    | {1,10}   | {5,6}    | {1,6}    | {2,2}    | {4,7}
 t       | t       | f       | t       | {{NULL,7},{1,10},{NULL,6}} | {{1,NULL},{2,2},{4,NULL}} | {NULL,7} | {1,10}   | {NULL,6} | {1,NULL} | {2,2}    | {4,NULL}
 t       | t       | t       | t       | {{5,6},{7,8},{9,10}}       | {{1,2},{3,4},{5,6}}       | {5,6}    | {7,8}    | {9,10}   | {1,2}    | {3,4}    | {5,6}
 f       | f       | f       | f       | {{1,2},{3,4},{5,6}}        | {{5,6},{7,8},{9,10}}      | {1,2}    | {3,4}    | {5,6}    | {5,6}    | {7,8}    | {9,10}
 f       | f       | f       | f       | {{1,2},{3,4},{5,6}}        | {{1,2},{3,4},{5,6}}       | {1,2}    | {3,4}    | {5,6}    | {1,2}    | {3,4}    | {5,6}
 f       | f       | f       | f       | {{7,8},{NULL,4},{5,NULL}}  | {{7,8},{NULL,4},{5,NULL}} | {7,8}    | {NULL,4} | {5,NULL} | {7,8}    | {NULL,4} | {5,NULL}
         |         |         |         |                            | {{1,2},{3,4},{5,6}}       |          |          |          | {1,2}    | {3,4}    | {5,6}
(8 rows)

postgres=# select long_nested_arrays.a >= long_nested_arrays.b as "a1 >= b1", long_arrays.a >= long_arrays.d as "a2 >= d2", long_arrays.b >= long_arrays.e as "b2 >= e2", long_arrays.c >= long_arrays.f as "c2 >= f2", long_nested_arrays.a as a1, long_nested_arrays.b as b1, long_arrays.a as a2, long_arrays.b as b2, long_arrays.c as c2,
 long_arrays.d as d2, long_arrays.e as e2, long_arrays.f as f2 from long_arrays inner join long_nested_arrays on long_arrays.id = long_nested_arrays.id;
 a1 >= b1 | a2 >= d2 | b2 >= e2 | c2 >= f2 |             a1             |            b1             |    a2    |    b2    |    c2    |    d2    |    e2    |    f2    
----------+----------+----------+----------+----------------------------+---------------------------+----------+----------+----------+----------+----------+----------
 f        | f        | t        | t        | {{1,2},{6,8},{4,7}}        | {{3,4},{2,4},{3,5}}       | {1,2}    | {6,8}    | {4,7}    | {3,4}    | {2,4}    | {3,5}
 t        | t        | f        | t        | {{5,7},{1,10},{5,6}}       | {{1,6},{2,2},{4,7}}       | {5,7}    | {1,10}   | {5,6}    | {1,6}    | {2,2}    | {4,7}
 t        | t        | f        | t        | {{NULL,7},{1,10},{NULL,6}} | {{1,NULL},{2,2},{4,NULL}} | {NULL,7} | {1,10}   | {NULL,6} | {1,NULL} | {2,2}    | {4,NULL}
 t        | t        | t        | t        | {{5,6},{7,8},{9,10}}       | {{1,2},{3,4},{5,6}}       | {5,6}    | {7,8}    | {9,10}   | {1,2}    | {3,4}    | {5,6}
 f        | f        | f        | f        | {{1,2},{3,4},{5,6}}        | {{5,6},{7,8},{9,10}}      | {1,2}    | {3,4}    | {5,6}    | {5,6}    | {7,8}    | {9,10}
 t        | t        | t        | t        | {{1,2},{3,4},{5,6}}        | {{1,2},{3,4},{5,6}}       | {1,2}    | {3,4}    | {5,6}    | {1,2}    | {3,4}    | {5,6}
 t        | t        | t        | t        | {{7,8},{NULL,4},{5,NULL}}  | {{7,8},{NULL,4},{5,NULL}} | {7,8}    | {NULL,4} | {5,NULL} | {7,8}    | {NULL,4} | {5,NULL}
          |          |          |          |                            | {{1,2},{3,4},{5,6}}       |          |          |          | {1,2}    | {3,4}    | {5,6}
(8 rows)

postgres=# select long_nested_arrays.a IS DISTINCT FROM long_nested_arrays.b as "a1 IS DISTINCT FROM b1", long_arrays.a IS DISTINCT FROM long_arrays.d as "a2 IS DISTINCT FROM d2", long_arrays.b IS DISTINCT FROM long_arrays.e as "b2 IS DISTINCT FROM e2", long_arrays.c IS DISTINCT FROM long_arrays.f as "c2 IS DISTINCT FROM f2", long_nested_arrays.a as a1, long_nested_arrays.b as b1, long_arrays.a as a2, long_arrays.b as b2, long_arrays.c as c2,
 long_arrays.d as d2, long_arrays.e as e2, long_arrays.f as f2 from long_arrays inner join long_nested_arrays on long_arrays.id = long_nested_arrays.id;
 a1 IS DISTINCT FROM b1 | a2 IS DISTINCT FROM d2 | b2 IS DISTINCT FROM e2 | c2 IS DISTINCT FROM f2 |             a1             |            b1             |    a2    |    b2    |    c2    |    d2    |    e2    |    f2    
------------------------+------------------------+------------------------+------------------------+----------------------------+---------------------------+----------+----------+----------+----------+----------+----------
 t                      | t                      | t                      | t                      | {{1,2},{6,8},{4,7}}        | {{3,4},{2,4},{3,5}}       | {1,2}    | {6,8}    | {4,7}    | {3,4}    | {2,4}    | {3,5}
 t                      | t                      | t                      | t                      | {{5,7},{1,10},{5,6}}       | {{1,6},{2,2},{4,7}}       | {5,7}    | {1,10}   | {5,6}    | {1,6}    | {2,2}    | {4,7}
 t                      | t                      | t                      | t                      | {{NULL,7},{1,10},{NULL,6}} | {{1,NULL},{2,2},{4,NULL}} | {NULL,7} | {1,10}   | {NULL,6} | {1,NULL} | {2,2}    | {4,NULL}
 t                      | t                      | t                      | t                      | {{5,6},{7,8},{9,10}}       | {{1,2},{3,4},{5,6}}       | {5,6}    | {7,8}    | {9,10}   | {1,2}    | {3,4}    | {5,6}
 t                      | t                      | t                      | t                      | {{1,2},{3,4},{5,6}}        | {{5,6},{7,8},{9,10}}      | {1,2}    | {3,4}    | {5,6}    | {5,6}    | {7,8}    | {9,10}
 f                      | f                      | f                      | f                      | {{1,2},{3,4},{5,6}}        | {{1,2},{3,4},{5,6}}       | {1,2}    | {3,4}    | {5,6}    | {1,2}    | {3,4}    | {5,6}
 f                      | f                      | f                      | f                      | {{7,8},{NULL,4},{5,NULL}}  | {{7,8},{NULL,4},{5,NULL}} | {7,8}    | {NULL,4} | {5,NULL} | {7,8}    | {NULL,4} | {5,NULL}
 t                      | t                      | t                      | t                      |                            | {{1,2},{3,4},{5,6}}       |          |          |          | {1,2}    | {3,4}    | {5,6}
(8 rows)

postgres=# select long_nested_arrays.a IS NOT DISTINCT FROM long_nested_arrays.b as "a1 IS NOT DISTINCT FROM b1", long_arrays.a IS NOT DISTINCT FROM long_arrays.d as "a2 IS NOT DISTINCT FROM d2", long_arrays.b IS NOT DISTINCT FROM long_arrays.e as "b2 
IS NOT DISTINCT FROM e2", long_arrays.c IS NOT DISTINCT FROM long_arrays.f as "c2 IS NOT DISTINCT FROM f2", long_nested_arrays.a as a1, long_nested_arrays.b as b1, long_arrays.a as a2, long_arrays.b as b2, long_arrays.c as c2,
 long_arrays.d as d2, long_arrays.e as e2, long_arrays.f as f2 from long_arrays inner join long_nested_arrays on long_arrays.id = long_nested_arrays.id;
 a1 IS NOT DISTINCT FROM b1 | a2 IS NOT DISTINCT FROM d2 | b2 IS NOT DISTINCT FROM e2 | c2 IS NOT DISTINCT FROM f2 |             a1             |            b1             |    a2    |    b2    |    c2    |    d2    |    e2    |    f2    
----------------------------+----------------------------+----------------------------+----------------------------+----------------------------+---------------------------+----------+----------+----------+----------+----------+----------
 f                          | f                          | f                          | f                          | {{1,2},{6,8},{4,7}}        | {{3,4},{2,4},{3,5}}       | {1,2}    | {6,8}    | {4,7}    | {3,4}    | {2,4}    | {3,5}
 f                          | f                          | f                          | f                          | {{5,7},{1,10},{5,6}}       | {{1,6},{2,2},{4,7}}       | {5,7}    | {1,10}   | {5,6}    | {1,6}    | {2,2}    | {4,7}
 f                          | f                          | f                          | f                          | {{NULL,7},{1,10},{NULL,6}} | {{1,NULL},{2,2},{4,NULL}} | {NULL,7} | {1,10}   | {NULL,6} | {1,NULL} | {2,2}    | {4,NULL}
 f                          | f                          | f                          | f                          | {{5,6},{7,8},{9,10}}       | {{1,2},{3,4},{5,6}}       | {5,6}    | {7,8}    | {9,10}   | {1,2}    | {3,4}    | {5,6}
 f                          | f                          | f                          | f                          | {{1,2},{3,4},{5,6}}        | {{5,6},{7,8},{9,10}}      | {1,2}    | {3,4}    | {5,6}    | {5,6}    | {7,8}    | {9,10}
 t                          | t                          | t                          | t                          | {{1,2},{3,4},{5,6}}        | {{1,2},{3,4},{5,6}}       | {1,2}    | {3,4}    | {5,6}    | {1,2}    | {3,4}    | {5,6}
 t                          | t                          | t                          | t                          | {{7,8},{NULL,4},{5,NULL}}  | {{7,8},{NULL,4},{5,NULL}} | {7,8}    | {NULL,4} | {5,NULL} | {7,8}    | {NULL,4} | {5,NULL}
 f                          | f                          | f                          | f                          |                            | {{1,2},{3,4},{5,6}}       |          |          |          | {1,2}    | {3,4}    | {5,6}
(8 rows)

Between array and scalar:

postgres=# select b < array[3, 4] from arrays;
 ?column? 
----------
 f
 t
 t
 f
 f
 f
 f
 
(8 rows)

postgres=# select array[3, 4] < b from arrays;
 ?column? 
----------
 t
 f
 f
 t
 f
 f
 t
 
(8 rows)

postgres=# select b < array[5, 6] from arrays;
 ?column? 
----------
 f
 t
 t
 f
 t
 t
 f
 
(8 rows)

postgres=# select array[5, 6] < b from arrays;
 ?column? 
----------
 t
 f
 f
 t
 f
 f
 t
 
(8 rows)

postgres=# select b <= array[3, 4] from arrays;
 ?column? 
----------
 f
 t
 t
 f
 t
 t
 f
 
(8 rows)

postgres=# select array[3, 4] <= b from arrays;
 ?column? 
----------
 t
 f
 f
 t
 t
 t
 t
 
(8 rows)

postgres=# select b <= array[5, 6] from arrays;
 ?column? 
----------
 f
 t
 t
 f
 t
 t
 f
 
(8 rows)

postgres=# select array[5, 6] <= b from arrays;
 ?column? 
----------
 t
 f
 f
 t
 f
 f
 t
 
(8 rows)

postgres=# select b = array[3, 4] from arrays;
 ?column? 
----------
 f
 f
 f
 f
 t
 t
 f
 
(8 rows)

postgres=# select array[3, 4] = b from arrays;
 ?column? 
----------
 f
 f
 f
 f
 t
 t
 f
 
(8 rows)

postgres=# select b = array[5, 6] from arrays;
 ?column? 
----------
 f
 f
 f
 f
 f
 f
 f
 
(8 rows)

postgres=# select array[5, 6] = b from arrays;
 ?column? 
----------
 f
 f
 f
 f
 f
 f
 f
 
(8 rows)

postgres=# select b <> array[3, 4] from arrays;
 ?column? 
----------
 t
 t
 t
 t
 f
 f
 t
 
(8 rows)

postgres=# select array[3, 4] <> b from arrays;
 ?column? 
----------
 t
 t
 t
 t
 f
 f
 t
 
(8 rows)

postgres=# select b <> array[5, 6] from arrays;
 ?column? 
----------
 t
 t
 t
 t
 t
 t
 t
 
(8 rows)

postgres=# select array[5, 6] <> b from arrays;
 ?column? 
----------
 t
 t
 t
 t
 t
 t
 t
 
(8 rows)

postgres=# select b > array[3, 4] from arrays;
 ?column? 
----------
 t
 f
 f
 t
 f
 f
 t
 
(8 rows)

postgres=# select array[3, 4] > b from arrays;
 ?column? 
----------
 f
 t
 t
 f
 f
 f
 f
 
(8 rows)

postgres=# select b > array[5, 6] from arrays;
 ?column? 
----------
 t
 f
 f
 t
 f
 f
 t
 
(8 rows)

postgres=# select array[5, 6] > b from arrays;
 ?column? 
----------
 f
 t
 t
 f
 t
 t
 f
 
(8 rows)

postgres=# select b >= array[3, 4] from arrays;
 ?column? 
----------
 t
 f
 f
 t
 t
 t
 t
 
(8 rows)

postgres=# select array[3, 4] >= b from arrays;
 ?column? 
----------
 f
 t
 t
 f
 t
 t
 f
 
(8 rows)

postgres=# select b >= array[5, 6] from arrays;
 ?column? 
----------
 t
 f
 f
 t
 f
 f
 t
 
(8 rows)

postgres=# select array[5, 6] >= b from arrays;
 ?column? 
----------
 f
 t
 t
 f
 t
 t
 f
 
(8 rows)

Describe alternatives you've considered

No response

Additional context

No response
@izveigor izveigor added the enhancement New feature or request label Jun 30, 2023
This was referenced Jun 30, 2023
Closed
Open
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
enhancement New feature or request
Projects
None yet
Milestone
No milestone
Development

Successfully merging a pull request may close this issue.

feat: array comparision
1 participant
@izveigor