arrays.out

postgresql8.3.4源码,开源数据库

----------
 f
(1 row)

select 33 >= all ('{1,2,33}');
 ?column? 
----------
 t
(1 row)

-- boundary cases
select null::int >= all ('{1,2,33}');
 ?column? 
----------
 
(1 row)

select null::int >= all ('{}');
 ?column? 
----------
 t
(1 row)

select null::int >= any ('{}');
 ?column? 
----------
 f
(1 row)

-- cross-datatype
select 33.4 = any (array[1,2,3]);
 ?column? 
----------
 f
(1 row)

select 33.4 > all (array[1,2,3]);
 ?column? 
----------
 t
(1 row)

-- errors
select 33 * any ('{1,2,3}');
ERROR:  op ANY/ALL (array) requires operator to yield boolean
LINE 1: select 33 * any ('{1,2,3}');
                  ^
select 33 * any (44);
ERROR:  op ANY/ALL (array) requires array on right side
LINE 1: select 33 * any (44);
                  ^
-- nulls
select 33 = any (null::int[]);
 ?column? 
----------
 
(1 row)

select null::int = any ('{1,2,3}');
 ?column? 
----------
 
(1 row)

select 33 = any ('{1,null,3}');
 ?column? 
----------
 
(1 row)

select 33 = any ('{1,null,33}');
 ?column? 
----------
 t
(1 row)

select 33 = all (null::int[]);
 ?column? 
----------
 
(1 row)

select null::int = all ('{1,2,3}');
 ?column? 
----------
 
(1 row)

select 33 = all ('{1,null,3}');
 ?column? 
----------
 f
(1 row)

select 33 = all ('{33,null,33}');
 ?column? 
----------
 
(1 row)

-- test indexes on arrays
create temp table arr_tbl (f1 int[] unique);
NOTICE:  CREATE TABLE / UNIQUE will create implicit index "arr_tbl_f1_key" for table "arr_tbl"
insert into arr_tbl values ('{1,2,3}');
insert into arr_tbl values ('{1,2}');
-- failure expected:
insert into arr_tbl values ('{1,2,3}');
ERROR:  duplicate key value violates unique constraint "arr_tbl_f1_key"
insert into arr_tbl values ('{2,3,4}');
insert into arr_tbl values ('{1,5,3}');
insert into arr_tbl values ('{1,2,10}');
set enable_seqscan to off;
set enable_bitmapscan to off;
select * from arr_tbl where f1 > '{1,2,3}' and f1 <= '{1,5,3}';
    f1    
----------
 {1,2,10}
 {1,5,3}
(2 rows)

-- note: if above select doesn't produce the expected tuple order,
-- then you didn't get an indexscan plan, and something is busted.
reset enable_seqscan;
reset enable_bitmapscan;
-- test [not] (like|ilike) (any|all) (...)
select 'foo' like any (array['%a', '%o']); -- t
 ?column? 
----------
 t
(1 row)

select 'foo' like any (array['%a', '%b']); -- f
 ?column? 
----------
 f
(1 row)

select 'foo' like all (array['f%', '%o']); -- t
 ?column? 
----------
 t
(1 row)

select 'foo' like all (array['f%', '%b']); -- f
 ?column? 
----------
 f
(1 row)

select 'foo' not like any (array['%a', '%b']); -- t
 ?column? 
----------
 t
(1 row)

select 'foo' not like all (array['%a', '%o']); -- f
 ?column? 
----------
 f
(1 row)

select 'foo' ilike any (array['%A', '%O']); -- t
 ?column? 
----------
 t
(1 row)

select 'foo' ilike all (array['F%', '%O']); -- t
 ?column? 
----------
 t
(1 row)

--
-- General array parser tests
--
-- none of the following should be accepted
select '{{1,{2}},{2,3}}'::text[];
ERROR:  malformed array literal: "{{1,{2}},{2,3}}"
select '{{},{}}'::text[];
ERROR:  malformed array literal: "{{},{}}"
select E'{{1,2},\\{2,3}}'::text[];
ERROR:  malformed array literal: "{{1,2},\{2,3}}"
select '{{"1 2" x},{3}}'::text[];
ERROR:  malformed array literal: "{{"1 2" x},{3}}"
select '{}}'::text[];
ERROR:  malformed array literal: "{}}"
select '{ }}'::text[];
ERROR:  malformed array literal: "{ }}"
-- none of the above should be accepted
-- all of the following should be accepted
select '{}'::text[];
 text 
------
 {}
(1 row)

select '{{{1,2,3,4},{2,3,4,5}},{{3,4,5,6},{4,5,6,7}}}'::text[];
                     text                      
-----------------------------------------------
 {{{1,2,3,4},{2,3,4,5}},{{3,4,5,6},{4,5,6,7}}}
(1 row)

select '{0 second  ,0 second}'::interval[];
   interval    
---------------
 {"@ 0","@ 0"}
(1 row)

select '{ { "," } , { 3 } }'::text[];
    text     
-------------
 {{","},{3}}
(1 row)

select '  {   {  "  0 second  "   ,  0 second  }   }'::text[];
             text              
-------------------------------
 {{"  0 second  ","0 second"}}
(1 row)

select '{
           0 second,
           @ 1 hour @ 42 minutes @ 20 seconds
         }'::interval[];
              interval              
------------------------------------
 {"@ 0","@ 1 hour 42 mins 20 secs"}
(1 row)

-- all of the above should be accepted
-- tests for array aggregates
CREATE TEMP TABLE arraggtest ( f1 INT[], f2 TEXT[][], f3 FLOAT[]);
INSERT INTO arraggtest (f1, f2, f3) VALUES
('{1,2,3,4}','{{grey,red},{blue,blue}}','{1.6, 0.0}');
INSERT INTO arraggtest (f1, f2, f3) VALUES
('{1,2,3}','{{grey,red},{grey,blue}}','{1.6}');
SELECT max(f1), min(f1), max(f2), min(f2), max(f3), min(f3) FROM arraggtest;
    max    |   min   |           max            |           min            |   max   |  min  
-----------+---------+--------------------------+--------------------------+---------+-------
 {1,2,3,4} | {1,2,3} | {{grey,red},{grey,blue}} | {{grey,red},{blue,blue}} | {1.6,0} | {1.6}
(1 row)

INSERT INTO arraggtest (f1, f2, f3) VALUES
('{3,3,2,4,5,6}','{{white,yellow},{pink,orange}}','{2.1,3.3,1.8,1.7,1.6}');
SELECT max(f1), min(f1), max(f2), min(f2), max(f3), min(f3) FROM arraggtest;
      max      |   min   |              max               |           min            |          max          |  min  
---------------+---------+--------------------------------+--------------------------+-----------------------+-------
 {3,3,2,4,5,6} | {1,2,3} | {{white,yellow},{pink,orange}} | {{grey,red},{blue,blue}} | {2.1,3.3,1.8,1.7,1.6} | {1.6}
(1 row)

INSERT INTO arraggtest (f1, f2, f3) VALUES
('{2}','{{black,red},{green,orange}}','{1.6,2.2,2.6,0.4}');
SELECT max(f1), min(f1), max(f2), min(f2), max(f3), min(f3) FROM arraggtest;
      max      |   min   |              max               |             min              |          max          |  min  
---------------+---------+--------------------------------+------------------------------+-----------------------+-------
 {3,3,2,4,5,6} | {1,2,3} | {{white,yellow},{pink,orange}} | {{black,red},{green,orange}} | {2.1,3.3,1.8,1.7,1.6} | {1.6}
(1 row)

INSERT INTO arraggtest (f1, f2, f3) VALUES
('{4,2,6,7,8,1}','{{red},{black},{purple},{blue},{blue}}',NULL);
SELECT max(f1), min(f1), max(f2), min(f2), max(f3), min(f3) FROM arraggtest;
      max      |   min   |              max               |             min              |          max          |  min  
---------------+---------+--------------------------------+------------------------------+-----------------------+-------
 {4,2,6,7,8,1} | {1,2,3} | {{white,yellow},{pink,orange}} | {{black,red},{green,orange}} | {2.1,3.3,1.8,1.7,1.6} | {1.6}
(1 row)

INSERT INTO arraggtest (f1, f2, f3) VALUES
('{}','{{pink,white,blue,red,grey,orange}}','{2.1,1.87,1.4,2.2}');
SELECT max(f1), min(f1), max(f2), min(f2), max(f3), min(f3) FROM arraggtest;
      max      | min |              max               |             min              |          max          |  min  
---------------+-----+--------------------------------+------------------------------+-----------------------+-------
 {4,2,6,7,8,1} | {}  | {{white,yellow},{pink,orange}} | {{black,red},{green,orange}} | {2.1,3.3,1.8,1.7,1.6} | {1.6}
(1 row)

-- A few simple tests for arrays of composite types
create type comptype as (f1 int, f2 text);
create table comptable (c1 comptype, c2 comptype[]);
-- XXX would like to not have to specify row() construct types here ...
insert into comptable
  values (row(1,'foo'), array[row(2,'bar')::comptype, row(3,'baz')::comptype]);
-- check that implicitly named array type _comptype isn't a problem
create type _comptype as enum('fooey');
select * from comptable;
   c1    |          c2           
---------+-----------------------
 (1,foo) | {"(2,bar)","(3,baz)"}
(1 row)

select c2[2].f2 from comptable;
 f2  
-----
 baz
(1 row)

drop type _comptype;
drop table comptable;
drop type comptype;