arrays.out

来自「PostgreSQL7.4.6 for Linux」· OUT 代码 · 共 380 行

--
-- ARRAYS
--
CREATE TABLE arrtest (
	a 			int2[],
	b 			int4[][][],
	c 			name[],
	d			text[][], 
	e 			float8[],
	f			char(5)[],
	g			varchar(5)[]
);
--
-- only this array as a 0-based 'e', the others are 1-based.
-- 'e' is also a large object.
--
INSERT INTO arrtest (a[5], b[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[2][2][1], 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][2], c, d[2][1])
   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}}} | {}        | {}            |           | {}              | {}
 {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 |        |      |  
 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 |        |      |  
 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)

--
-- 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           
---------------------------
 {{{{{{hello},{world}}}}}}
(1 row)

SELECT ARRAY[ARRAY['hello'],ARRAY['world']];
       array       
-------------------
 {{hello},{world}}
(1 row)

SELECT ARRAY(select f2 from arrtest_f order by f2) AS "ARRAY";
                     ARRAY                     
-----------------------------------------------
 {1.15,1.15,1.18,1.21,1.24,1.26,1.26,1.3,1.32}
(1 row)

-- functions
SELECT array_append(array[42], 6) AS "{42,6}";
 {42,6} 
--------
 {42,6}
(1 row)

SELECT array_prepend(6, array[42]) AS "{6,42}";
 {6,42} 
--------
 {6,42}
(1 row)

SELECT array_cat(ARRAY[1,2], ARRAY[3,4]) AS "{1,2,3,4}";
 {1,2,3,4} 
-----------
 {1,2,3,4}
(1 row)

SELECT array_cat(ARRAY[1,2], ARRAY[[3,4],[5,6]]) AS "{{1,2},{3,4},{5,6}}";
 {{1,2},{3,4},{5,6}} 
---------------------
 {{1,2},{3,4},{5,6}}
(1 row)

SELECT array_cat(ARRAY[[3,4],[5,6]], ARRAY[1,2]) AS "{{3,4},{5,6},{1,2}}";
 {{3,4},{5,6},{1,2}} 
---------------------
 {{3,4},{5,6},{1,2}}
(1 row)

-- operators
SELECT a FROM arrtest WHERE b = ARRAY[[[113,142],[1,147]]];
       a       
---------------
 {16,25,3,4,5}
(1 row)

SELECT NOT ARRAY[1.1,1.2,1.3] = ARRAY[1.1,1.2,1.3] AS "FALSE";
 FALSE 
-------
 f
(1 row)

SELECT ARRAY[1,2] || 3 AS "{1,2,3}";
 {1,2,3} 
---------
 {1,2,3}
(1 row)

SELECT 0 || ARRAY[1,2] AS "{0,1,2}";
 {0,1,2} 
---------
 {0,1,2}
(1 row)

SELECT ARRAY[1,2] || ARRAY[3,4] AS "{1,2,3,4}";
 {1,2,3,4} 
-----------
 {1,2,3,4}
(1 row)

SELECT ARRAY[[['hello','world']]] || ARRAY[[['happy','birthday']]] AS "ARRAY";
                ARRAY                 
--------------------------------------
 {{{hello,world}},{{happy,birthday}}}
(1 row)

SELECT ARRAY[[1,2],[3,4]] || ARRAY[5,6] AS "{{1,2},{3,4},{5,6}}";
 {{1,2},{3,4},{5,6}} 
---------------------
 {{1,2},{3,4},{5,6}}
(1 row)

SELECT ARRAY[0,0] || ARRAY[1,1] || ARRAY[2,2] AS "{0,0,1,1,2,2}";
 {0,0,1,1,2,2} 
---------------
 {0,0,1,1,2,2}
(1 row)

SELECT 0 || ARRAY[1,2] || 3 AS "{0,1,2,3}";
 {0,1,2,3} 
-----------
 {0,1,2,3}
(1 row)

-- array casts
SELECT ARRAY[1,2,3]::text[]::int[]::float8[] AS "{1,2,3}";
 {1,2,3} 
---------
 {1,2,3}
(1 row)

SELECT ARRAY[1,2,3]::text[]::int[]::float8[] is of (float8[]) as "TRUE";
 TRUE 
------
 t
(1 row)

SELECT ARRAY[['a','bc'],['def','hijk']]::text[]::varchar[] AS "{{a,bc},{def,hijk}}";
 {{a,bc},{def,hijk}} 
---------------------
 {{a,bc},{def,hijk}}
(1 row)

SELECT ARRAY[['a','bc'],['def','hijk']]::text[]::varchar[] is of (varchar[]) as "TRUE";
 TRUE 
------
 t
(1 row)

SELECT CAST(ARRAY[[[[[['a','bb','ccc']]]]]] as text[]) as "{{{{{{a,bb,ccc}}}}}}";
 {{{{{{a,bb,ccc}}}}}} 
----------------------
 {{{{{{a,bb,ccc}}}}}}
(1 row)

-- scalar op any/all (array)
select 33 = any ('{1,2,3}');
 ?column? 
----------
 f
(1 row)

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

select 33 = all ('{1,2,33}');
 ?column? 
----------
 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
select 33 * any (44);
ERROR:  op ANY/ALL (array) requires array on right side
-- 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 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;
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.