Row and Array Comparisons

来源：互联网发布：python async def 编辑：程序博客网时间：2024/06/01 09:19

http://www.postgresql.org/docs/9.3/static/functions-comparisons.html#AEN18448

Row-wise Comparison：

row的定义：http://www.postgresql.org/docs/9.3/static/sql-expressions.html#SQL-SYNTAX-ROW-CONSTRUCTORS

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：http://www.postgresql.org/docs/9.3/static/arrays.html

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

demo：http://www.cnblogs.com/gaojian/p/3190737.html


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)

0 0