arrays.out

postgresql8.3.4源码,开源数据库

--
-- ARRAYS
--
CREATE TABLE arrtest (
	a 			int2[],
	b 			int4[][][],
	c 			name[],
	d			text[][], 
	e 			float8[],
	f			char(5)[],
	g			varchar(5)[]
);
--
-- only the 'e' array is 0-based, the others are 1-based.
--
INSERT INTO arrtest (a[1:5], b[1:1][1:2][1:2], c, d, f, g)
   VALUES ('{1,2,3,4,5}', '{{{0,0},{1,2}}}', '{}', '{}', '{}', '{}');
UPDATE arrtest SET e[0] = '1.1';
UPDATE arrtest SET e[1] = '2.2';
INSERT INTO arrtest (f)
   VALUES ('{"too long"}');
ERROR:  value too long for type character(5)
INSERT INTO arrtest (a, b[1:2][1:2], c, d, e, f, g)
   VALUES ('{11,12,23}', '{{3,4},{4,5}}', '{"foobar"}', 
           '{{"elt1", "elt2"}}', '{"3.4", "6.7"}',
           '{"abc","abcde"}', '{"abc","abcde"}');
INSERT INTO arrtest (a, b[1:2], c, d[1:2])
   VALUES ('{}', '{3,4}', '{foo,bar}', '{bar,foo}');
SELECT * FROM arrtest;
      a      |        b        |     c     |       d       |        e        |        f        |      g      
-------------+-----------------+-----------+---------------+-----------------+-----------------+-------------
 {1,2,3,4,5} | {{{0,0},{1,2}}} | {}        | {}            | [0:1]={1.1,2.2} | {}              | {}
 {11,12,23}  | {{3,4},{4,5}}   | {foobar}  | {{elt1,elt2}} | {3.4,6.7}       | {"abc  ",abcde} | {abc,abcde}
 {}          | {3,4}           | {foo,bar} | {bar,foo}     |                 |                 | 
(3 rows)

SELECT arrtest.a[1],
          arrtest.b[1][1][1],
          arrtest.c[1],
          arrtest.d[1][1], 
          arrtest.e[0]
   FROM arrtest;
 a  | b |   c    |  d   |  e  
----+---+--------+------+-----
  1 | 0 |        |      | 1.1
 11 |   | foobar | elt1 |    
    |   | foo    |      |    
(3 rows)

SELECT a[1], b[1][1][1], c[1], d[1][1], e[0]
   FROM arrtest;
 a  | b |   c    |  d   |  e  
----+---+--------+------+-----
  1 | 0 |        |      | 1.1
 11 |   | foobar | elt1 |    
    |   | foo    |      |    
(3 rows)

SELECT a[1:3],
          b[1:1][1:2][1:2],
          c[1:2], 
          d[1:1][1:2]
   FROM arrtest;
     a      |        b        |     c     |       d       
------------+-----------------+-----------+---------------
 {1,2,3}    | {{{0,0},{1,2}}} | {}        | {}
 {11,12,23} | {}              | {foobar}  | {{elt1,elt2}}
 {}         | {}              | {foo,bar} | {}
(3 rows)

SELECT array_dims(a) AS a,array_dims(b) AS b,array_dims(c) AS c
   FROM arrtest;
   a   |        b        |   c   
-------+-----------------+-------
 [1:5] | [1:1][1:2][1:2] | 
 [1:3] | [1:2][1:2]      | [1:1]
       | [1:2]           | [1:2]
(3 rows)

-- returns nothing 
SELECT *
   FROM arrtest
   WHERE a[1] < 5 and 
         c = '{"foobar"}'::_name;
 a | b | c | d | e | f | g 
---+---+---+---+---+---+---
(0 rows)

UPDATE arrtest
  SET a[1:2] = '{16,25}'
  WHERE NOT a = '{}'::_int2;
UPDATE arrtest
  SET b[1:1][1:1][1:2] = '{113, 117}',
      b[1:1][1:2][2:2] = '{142, 147}'
  WHERE array_dims(b) = '[1:1][1:2][1:2]';
UPDATE arrtest
  SET c[2:2] = '{"new_word"}'
  WHERE array_dims(c) is not null;
SELECT a,b,c FROM arrtest;
       a       |           b           |         c         
---------------+-----------------------+-------------------
 {16,25,3,4,5} | {{{113,142},{1,147}}} | {}
 {}            | {3,4}                 | {foo,new_word}
 {16,25,23}    | {{3,4},{4,5}}         | {foobar,new_word}
(3 rows)

SELECT a[1:3],
          b[1:1][1:2][1:2],
          c[1:2], 
          d[1:1][2:2]
   FROM arrtest;
     a      |           b           |         c         |    d     
------------+-----------------------+-------------------+----------
 {16,25,3}  | {{{113,142},{1,147}}} | {}                | {}
 {}         | {}                    | {foo,new_word}    | {}
 {16,25,23} | {}                    | {foobar,new_word} | {{elt2}}
(3 rows)

INSERT INTO arrtest(a) VALUES('{1,null,3}');
SELECT a FROM arrtest;
       a       
---------------
 {16,25,3,4,5}
 {}
 {16,25,23}
 {1,NULL,3}
(4 rows)

UPDATE arrtest SET a[4] = NULL WHERE a[2] IS NULL;
SELECT a FROM arrtest WHERE a[2] IS NULL;
        a        
-----------------
 [4:4]={NULL}
 {1,NULL,3,NULL}
(2 rows)

DELETE FROM arrtest WHERE a[2] IS NULL AND b IS NULL;
SELECT a,b,c FROM arrtest;
       a       |           b           |         c         
---------------+-----------------------+-------------------
 {16,25,3,4,5} | {{{113,142},{1,147}}} | {}
 {16,25,23}    | {{3,4},{4,5}}         | {foobar,new_word}
 [4:4]={NULL}  | {3,4}                 | {foo,new_word}
(3 rows)

--
-- test array extension
--
CREATE TEMP TABLE arrtest1 (i int[], t text[]);
insert into arrtest1 values(array[1,2,null,4], array['one','two',null,'four']);
select * from arrtest1;
      i       |          t          
--------------+---------------------
 {1,2,NULL,4} | {one,two,NULL,four}
(1 row)

update arrtest1 set i[2] = 22, t[2] = 'twenty-two';
select * from arrtest1;
       i       |             t              
---------------+----------------------------
 {1,22,NULL,4} | {one,twenty-two,NULL,four}
(1 row)

update arrtest1 set i[5] = 5, t[5] = 'five';
select * from arrtest1;
        i        |                t                
-----------------+---------------------------------
 {1,22,NULL,4,5} | {one,twenty-two,NULL,four,five}
(1 row)

update arrtest1 set i[8] = 8, t[8] = 'eight';
select * from arrtest1;
              i              |                        t                        
-----------------------------+-------------------------------------------------
 {1,22,NULL,4,5,NULL,NULL,8} | {one,twenty-two,NULL,four,five,NULL,NULL,eight}
(1 row)

update arrtest1 set i[0] = 0, t[0] = 'zero';
select * from arrtest1;
                  i                  |                             t                              
-------------------------------------+------------------------------------------------------------
 [0:8]={0,1,22,NULL,4,5,NULL,NULL,8} | [0:8]={zero,one,twenty-two,NULL,four,five,NULL,NULL,eight}
(1 row)

update arrtest1 set i[-3] = -3, t[-3] = 'minus-three';
select * from arrtest1;
                         i                         |                                         t                                         
---------------------------------------------------+-----------------------------------------------------------------------------------
 [-3:8]={-3,NULL,NULL,0,1,22,NULL,4,5,NULL,NULL,8} | [-3:8]={minus-three,NULL,NULL,zero,one,twenty-two,NULL,four,five,NULL,NULL,eight}
(1 row)

update arrtest1 set i[0:2] = array[10,11,12], t[0:2] = array['ten','eleven','twelve'];
select * from arrtest1;
                          i                          |                                        t                                        
-----------------------------------------------------+---------------------------------------------------------------------------------
 [-3:8]={-3,NULL,NULL,10,11,12,NULL,4,5,NULL,NULL,8} | [-3:8]={minus-three,NULL,NULL,ten,eleven,twelve,NULL,four,five,NULL,NULL,eight}
(1 row)

update arrtest1 set i[8:10] = array[18,null,20], t[8:10] = array['p18',null,'p20'];
select * from arrtest1;
                               i                               |                                            t                                            
---------------------------------------------------------------+-----------------------------------------------------------------------------------------
 [-3:10]={-3,NULL,NULL,10,11,12,NULL,4,5,NULL,NULL,18,NULL,20} | [-3:10]={minus-three,NULL,NULL,ten,eleven,twelve,NULL,four,five,NULL,NULL,p18,NULL,p20}
(1 row)

update arrtest1 set i[11:12] = array[null,22], t[11:12] = array[null,'p22'];
select * from arrtest1;
                                   i                                   |                                                t                                                 
-----------------------------------------------------------------------+--------------------------------------------------------------------------------------------------
 [-3:12]={-3,NULL,NULL,10,11,12,NULL,4,5,NULL,NULL,18,NULL,20,NULL,22} | [-3:12]={minus-three,NULL,NULL,ten,eleven,twelve,NULL,four,five,NULL,NULL,p18,NULL,p20,NULL,p22}
(1 row)

update arrtest1 set i[15:16] = array[null,26], t[15:16] = array[null,'p26'];
select * from arrtest1;
                                            i                                            |                                                          t                                                          
-----------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------
 [-3:16]={-3,NULL,NULL,10,11,12,NULL,4,5,NULL,NULL,18,NULL,20,NULL,22,NULL,NULL,NULL,26} | [-3:16]={minus-three,NULL,NULL,ten,eleven,twelve,NULL,four,five,NULL,NULL,p18,NULL,p20,NULL,p22,NULL,NULL,NULL,p26}
(1 row)

update arrtest1 set i[-5:-3] = array[-15,-14,-13], t[-5:-3] = array['m15','m14','m13'];
select * from arrtest1;
                                                i                                                 |                                                          t                                                          
--------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------
 [-5:16]={-15,-14,-13,NULL,NULL,10,11,12,NULL,4,5,NULL,NULL,18,NULL,20,NULL,22,NULL,NULL,NULL,26} | [-5:16]={m15,m14,m13,NULL,NULL,ten,eleven,twelve,NULL,four,five,NULL,NULL,p18,NULL,p20,NULL,p22,NULL,NULL,NULL,p26}
(1 row)

update arrtest1 set i[-7:-6] = array[-17,null], t[-7:-6] = array['m17',null];
select * from arrtest1;
                                                     i                                                     |                                                              t                                                               
-----------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------
 [-7:16]={-17,NULL,-15,-14,-13,NULL,NULL,10,11,12,NULL,4,5,NULL,NULL,18,NULL,20,NULL,22,NULL,NULL,NULL,26} | [-7:16]={m17,NULL,m15,m14,m13,NULL,NULL,ten,eleven,twelve,NULL,four,five,NULL,NULL,p18,NULL,p20,NULL,p22,NULL,NULL,NULL,p26}
(1 row)

update arrtest1 set i[-12:-10] = array[-22,null,-20], t[-12:-10] = array['m22',null,'m20'];
select * from arrtest1;
                                                                 i                                                                 |                                                                          t                                                                           
-----------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------
 [-12:16]={-22,NULL,-20,NULL,NULL,-17,NULL,-15,-14,-13,NULL,NULL,10,11,12,NULL,4,5,NULL,NULL,18,NULL,20,NULL,22,NULL,NULL,NULL,26} | [-12:16]={m22,NULL,m20,NULL,NULL,m17,NULL,m15,m14,m13,NULL,NULL,ten,eleven,twelve,NULL,four,five,NULL,NULL,p18,NULL,p20,NULL,p22,NULL,NULL,NULL,p26}
(1 row)

delete from arrtest1;
insert into arrtest1 values(array[1,2,null,4], array['one','two',null,'four']);
select * from arrtest1;
      i       |          t          
--------------+---------------------
 {1,2,NULL,4} | {one,two,NULL,four}
(1 row)

update arrtest1 set i[0:5] = array[0,1,2,null,4,5], t[0:5] = array['z','p1','p2',null,'p4','p5'];
select * from arrtest1;
           i            |             t              
------------------------+----------------------------
 [0:5]={0,1,2,NULL,4,5} | [0:5]={z,p1,p2,NULL,p4,p5}
(1 row)

--
-- array expressions and operators
--
-- table creation and INSERTs
CREATE TEMP TABLE arrtest2 (i integer ARRAY[4], f float8[], n numeric[], t text[], d timestamp[]);
INSERT INTO arrtest2 VALUES(
  ARRAY[[[113,142],[1,147]]],
  ARRAY[1.1,1.2,1.3]::float8[],
  ARRAY[1.1,1.2,1.3],
  ARRAY[[['aaa','aab'],['aba','abb'],['aca','acb']],[['baa','bab'],['bba','bbb'],['bca','bcb']]],
  ARRAY['19620326','19931223','19970117']::timestamp[]
);
-- some more test data
CREATE TEMP TABLE arrtest_f (f0 int, f1 text, f2 float8);
insert into arrtest_f values(1,'cat1',1.21);
insert into arrtest_f values(2,'cat1',1.24);
insert into arrtest_f values(3,'cat1',1.18);
insert into arrtest_f values(4,'cat1',1.26);
insert into arrtest_f values(5,'cat1',1.15);
insert into arrtest_f values(6,'cat2',1.15);
insert into arrtest_f values(7,'cat2',1.26);
insert into arrtest_f values(8,'cat2',1.32);
insert into arrtest_f values(9,'cat2',1.30);
CREATE TEMP TABLE arrtest_i (f0 int, f1 text, f2 int);
insert into arrtest_i values(1,'cat1',21);
insert into arrtest_i values(2,'cat1',24);
insert into arrtest_i values(3,'cat1',18);
insert into arrtest_i values(4,'cat1',26);
insert into arrtest_i values(5,'cat1',15);
insert into arrtest_i values(6,'cat2',15);
insert into arrtest_i values(7,'cat2',26);
insert into arrtest_i values(8,'cat2',32);
insert into arrtest_i values(9,'cat2',30);
-- expressions
SELECT t.f[1][3][1] AS "131", t.f[2][2][1] AS "221" FROM (
  SELECT ARRAY[[[111,112],[121,122],[131,132]],[[211,212],[221,122],[231,232]]] AS f
) AS t;
 131 | 221 
-----+-----
 131 | 221
(1 row)

SELECT ARRAY[[[[[['hello'],['world']]]]]];
           array