Row and Array Comparisons

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 TABLE
digoal=# insert into tx values(1, '{1,2,3,4}', '{{1,2,3,4,5},{1,2,3,4,5}}');
INSERT 0 1
digoal=# 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 1
digoal=# 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 1

digoal=# select 2= any( array(select fooid from foo));
 ?column? 
----------
 t
(1 row)