Row and Array Comparisons-白红宇

Row and Array Comparisons

阅读量：4582 次

发布时间：2019-06-09

本文共 2384 字，大约阅读时间需要 7 分钟。

Row-wise Comparison：

row的定义：

row与row的比较可以使用符号：

=, <>, <, <=, > or >=

digoal=# select row(a.foosubid) from foo a limit 1;                             row ----- (2)(1 row)digoal=# select row(a.foosubid) = row(2) from foo a limit 1;   ?column? ---------- t(1 row)digoal=# select row(a.foosubid) = row('2') from foo a limit 1;  ?column? ---------- t(1 row)digoal=# select row(a.foosubid) = row(22) from foo a limit 1; ?column? ---------- f(1 row)

is distinct from：

digoal=# select row(a.foosubid) is distinct from row(1) from foo a limit 1;  ?column? ---------- t(1 row)digoal=# select row(a.foosubid) is distinct from row(2) from foo a limit 1;           ?column? ---------- f(1 row)

Any，All:

array：

expression operator ANY (array expression) expression operator SOME (array expression)

expression operator ALL (array expression)

demo：

digoal=# create table tx (a int, b int[], c int[][]);     CREATE TABLEdigoal=# insert into tx values(1, '{1,2,3,4}', '{
    {1,2,3,4,5},{1,2,3,4,5}}');INSERT 0 1digoal=# insert into tx values(1, '{1,2,3,4}', '{
    {1,2,3,4,5},{1,2,3,4,5},{1,2,3,4,5}}');INSERT 0 1digoal=# insert into tx values(1, '{1,2,3,4}', '{
    {1,2,3,4,5},{1,2,3,4,5},{1,2,3,4,5},{4,2}}');ERROR:  malformed array literal: "{
    {1,2,3,4,5},{1,2,3,4,5},{1,2,3,4,5},{4,}}"LINE 1: insert into tx values(1, '{1,2,3,4}', '{
    {1,2,3,4,5},{1,2,3,4...                                              ^digoal=# select * from tx; a |     b     |                   c                   ---+-----------+--------------------------------------- 1 | {1,2,3,4} | {
    {1,2,3,4,5},{1,2,3,4,5}} 1 | {1,2,3,4} | {
    {1,2,3,4,5},{1,2,3,4,5},{1,2,3,4,5}}(2 rows)digoal=# select c[1][1] from tx;    c --- 1 1(2 rows)digoal=# select c[0][0] from tx;   c ---    (2 rows)digoal=# select c[1][2] from tx;   c --- 2 2(2 rows)digoal=# select * from tx where 4 =any( c); a |     b     |                   c                   ---+-----------+--------------------------------------- 1 | {1,2,3,4} | {
    {1,2,3,4,5},{1,2,3,4,5}} 1 | {1,2,3,4} | {
    {1,2,3,4,5},{1,2,3,4,5},{1,2,3,4,5}}(2 rows)digoal=# select * from tx where 4 =all( c);    a | b | c ---+---+---(0 rows)digoal=# insert into tx values(1, '{1,2,3,4}', array[[1,2,3,4,5],[1,2,3,4,5],[1,2,3,4,5]]); INSERT 0 1digoal=# select 2= any( array(select fooid from foo)); ?column? ---------- t(1 row)