Google Git
Sign in
apache / cloudberry / refs/heads/cbdb-postgres-merge / . / src / test / regress / expected / arrays.out
blob: 20b401a9ef979a39762f66c51df9590aaeec0a1a [file] [log] [blame]
--
-- ARRAYS
--
-- directory paths are passed to us in environment variables
\getenv abs_srcdir PG_ABS_SRCDIR
CREATE TABLE arrtest (
a int2[],
b int4[][][],
c name[],
d text[][],
e float8[],
f char(5)[],
g varchar(5)[]
) DISTRIBUTED RANDOMLY;
CREATE TABLE array_op_test (
seqno int4,
i int4[],
t text[]
);
\set filename :abs_srcdir '/data/array.data'
COPY array_op_test FROM :'filename';
ANALYZE array_op_test;
--
-- 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}');
INSERT INTO arrtest (b[2]) VALUES(now()); -- error, type mismatch
ERROR: subscripted assignment to "b" requires type integer but expression is of type timestamp with time zone
LINE 1: INSERT INTO arrtest (b[2]) VALUES(now());
^
HINT: You will need to rewrite or cast the expression.
INSERT INTO arrtest (b[1:2]) VALUES(now()); -- error, type mismatch
ERROR: subscripted assignment to "b" requires type integer[] but expression is of type timestamp with time zone
LINE 1: INSERT INTO arrtest (b[1:2]) VALUES(now());
^
HINT: You will need to rewrite or cast the expression.
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_ndims(a) AS a,array_ndims(b) AS b,array_ndims(c) AS c
FROM arrtest;
a | b | c
---+---+---
1 | 3 |
1 | 2 | 1
| 1 | 1
(3 rows)
SELECT array_ndims(a) AS a,array_ndims(b) AS b,array_ndims(c) AS c
FROM arrtest;
a | b | c
---+---+---
1 | 3 |
1 | 2 | 1
| 1 | 1
(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)
SELECT b[1:1][2][2],
d[1:1][2]
FROM arrtest;
b | d
-----------------------+---------------
{{{113,142},{1,147}}} | {}
{} | {}
{} | {{elt1,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 non-error-throwing API
SELECT pg_input_is_valid('{1,2,3}', 'integer[]');
pg_input_is_valid
-------------------
t
(1 row)
SELECT pg_input_is_valid('{1,2', 'integer[]');
pg_input_is_valid
-------------------
f
(1 row)
SELECT pg_input_is_valid('{1,zed}', 'integer[]');
pg_input_is_valid
-------------------
f
(1 row)
SELECT * FROM pg_input_error_info('{1,zed}', 'integer[]');
message | detail | hint | sql_error_code
----------------------------------------------+--------+------+----------------
invalid input syntax for type integer: "zed" | | | 22P02
(1 row)
-- test mixed slice/scalar subscripting
select '{{1,2,3},{4,5,6},{7,8,9}}'::int[];
int4
---------------------------
{{1,2,3},{4,5,6},{7,8,9}}
(1 row)
select ('{{1,2,3},{4,5,6},{7,8,9}}'::int[])[1:2][2];
int4
---------------
{{1,2},{4,5}}
(1 row)
select '[0:2][0:2]={{1,2,3},{4,5,6},{7,8,9}}'::int[];
int4
--------------------------------------
[0:2][0:2]={{1,2,3},{4,5,6},{7,8,9}}
(1 row)
select ('[0:2][0:2]={{1,2,3},{4,5,6},{7,8,9}}'::int[])[1:2][2];
int4
---------------
{{5,6},{8,9}}
(1 row)
--
-- check subscription corner cases
--
-- More subscripts than MAXDIM (6)
SELECT ('{}'::int[])[1][2][3][4][5][6][7];
ERROR: number of array dimensions (7) exceeds the maximum allowed (6)
-- NULL index yields NULL when selecting
SELECT ('{{{1},{2},{3}},{{4},{5},{6}}}'::int[])[1][NULL][1];
int4
------
(1 row)
SELECT ('{{{1},{2},{3}},{{4},{5},{6}}}'::int[])[1][NULL:1][1];
int4
------
(1 row)
SELECT ('{{{1},{2},{3}},{{4},{5},{6}}}'::int[])[1][1:NULL][1];
int4
------
(1 row)
-- NULL index in assignment is an error
UPDATE arrtest
SET c[NULL] = '{"can''t assign"}'
WHERE array_dims(c) is not null;
ERROR: array subscript in assignment must not be null
UPDATE arrtest
SET c[NULL:1] = '{"can''t assign"}'
WHERE array_dims(c) is not null;
ERROR: array subscript in assignment must not be null
UPDATE arrtest
SET c[1:NULL] = '{"can''t assign"}'
WHERE array_dims(c) is not null;
ERROR: array subscript in assignment must not be null
-- Un-subscriptable type
SELECT (now())[1];
ERROR: cannot subscript type timestamp with time zone because it does not support subscripting
LINE 1: SELECT (now())[1];
^
-- test slices with empty lower and/or upper index
CREATE TEMP TABLE arrtest_s (
a int2[],
b int2[][]
);
INSERT INTO arrtest_s VALUES ('{1,2,3,4,5}', '{{1,2,3}, {4,5,6}, {7,8,9}}');
INSERT INTO arrtest_s VALUES ('[0:4]={1,2,3,4,5}', '[0:2][0:2]={{1,2,3}, {4,5,6}, {7,8,9}}');
SELECT * FROM arrtest_s;
a | b
-------------------+--------------------------------------
{1,2,3,4,5} | {{1,2,3},{4,5,6},{7,8,9}}
[0:4]={1,2,3,4,5} | [0:2][0:2]={{1,2,3},{4,5,6},{7,8,9}}
(2 rows)
SELECT a[:3], b[:2][:2] FROM arrtest_s;
a | b
-----------+---------------------------
{1,2,3} | {{1,2},{4,5}}
{1,2,3,4} | {{1,2,3},{4,5,6},{7,8,9}}
(2 rows)
SELECT a[2:], b[2:][2:] FROM arrtest_s;
a | b
-----------+---------------
{2,3,4,5} | {{5,6},{8,9}}
{3,4,5} | {{9}}
(2 rows)
SELECT a[:], b[:] FROM arrtest_s;
a | b
-------------+---------------------------
{1,2,3,4,5} | {{1,2,3},{4,5,6},{7,8,9}}
{1,2,3,4,5} | {{1,2,3},{4,5,6},{7,8,9}}
(2 rows)
-- updates
UPDATE arrtest_s SET a[:3] = '{11, 12, 13}', b[:2][:2] = '{{11,12}, {14,15}}'
WHERE array_lower(a,1) = 1;
SELECT * FROM arrtest_s;
a | b
-------------------+--------------------------------------
[0:4]={1,2,3,4,5} | [0:2][0:2]={{1,2,3},{4,5,6},{7,8,9}}
{11,12,13,4,5} | {{11,12,3},{14,15,6},{7,8,9}}
(2 rows)
UPDATE arrtest_s SET a[3:] = '{23, 24, 25}', b[2:][2:] = '{{25,26}, {28,29}}';
SELECT * FROM arrtest_s;
a | b
---------------------+---------------------------------------
[0:4]={1,2,3,23,24} | [0:2][0:2]={{1,2,3},{4,5,6},{7,8,25}}
{11,12,23,24,25} | {{11,12,3},{14,25,26},{7,28,29}}
(2 rows)
UPDATE arrtest_s SET a[:] = '{11, 12, 13, 14, 15}';
SELECT * FROM arrtest_s;
a | b
------------------------+---------------------------------------
[0:4]={11,12,13,14,15} | [0:2][0:2]={{1,2,3},{4,5,6},{7,8,25}}
{11,12,13,14,15} | {{11,12,3},{14,25,26},{7,28,29}}
(2 rows)
UPDATE arrtest_s SET a[:] = '{23, 24, 25}'; -- fail, too small
ERROR: source array too small
INSERT INTO arrtest_s VALUES(NULL, NULL);
UPDATE arrtest_s SET a[:] = '{11, 12, 13, 14, 15}'; -- fail, no good with null
ERROR: array slice subscript must provide both boundaries
DETAIL: When assigning to a slice of an empty array value, slice boundaries must be fully specified.
-- we want to work with a point_tbl that includes a null
CREATE TEMP TABLE point_tbl AS SELECT * FROM public.point_tbl;
INSERT INTO POINT_TBL(f1) VALUES (NULL);
-- check with fixed-length-array type, such as point
SELECT f1[0:1] FROM POINT_TBL;
ERROR: slices of fixed-length arrays not implemented
SELECT f1[0:] FROM POINT_TBL;
ERROR: slices of fixed-length arrays not implemented
SELECT f1[:1] FROM POINT_TBL;
ERROR: slices of fixed-length arrays not implemented
SELECT f1[:] FROM POINT_TBL;
ERROR: slices of fixed-length arrays not implemented
-- subscript assignments to fixed-width result in NULL if previous value is NULL
UPDATE point_tbl SET f1[0] = 10 WHERE f1 IS NULL RETURNING *;
f1
----
(1 row)
INSERT INTO point_tbl(f1[0]) VALUES(0) RETURNING *;
f1
----
(1 row)
-- NULL assignments get ignored
UPDATE point_tbl SET f1[0] = NULL WHERE f1::text = '(10,10)'::point::text RETURNING *;
f1
---------
(10,10)
(1 row)
-- but non-NULL subscript assignments work
UPDATE point_tbl SET f1[0] = -10, f1[1] = -10 WHERE f1::text = '(10,10)'::point::text RETURNING *;
f1
-----------
(-10,-10)
(1 row)
-- but not to expand the range
UPDATE point_tbl SET f1[3] = 10 WHERE f1::text = '(-10,-10)'::point::text RETURNING *;
ERROR: array subscript out of range
--
-- test array extension
--
CREATE TEMP TABLE arrtest1 (i int[], t text[]) DISTRIBUTED RANDOMLY;
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[]) DISTRIBUTED RANDOMLY;
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) DISTRIBUTED RANDOMLY;
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) DISTRIBUTED RANDOMLY;
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" ORDER BY 1; -- MPP-11853
ARRAY
-----------------------------------------------
{1.15,1.15,1.18,1.21,1.24,1.26,1.26,1.3,1.32}
(1 row)
-- check no merge on Motion
EXPLAIN SELECT ARRAY(select f2 from arrtest_f) AS "ARRAY";
QUERY PLAN
------------------------------------------------------------------------------------
Result (cost=1.03..1.04 rows=1 width=32)
InitPlan 1 (returns $0) (slice1)
-> Gather Motion 3:1 (slice2; segments: 3) (cost=0.00..1.03 rows=1 width=8)
-> Seq Scan on arrtest_f (cost=0.00..1.01 rows=1 width=8)
Optimizer: Postgres query optimizer
(5 rows)
-- with nulls
SELECT '{1,null,3}'::int[];
int4
------------
{1,NULL,3}
(1 row)
SELECT ARRAY[1,NULL,3];
array
------------
{1,NULL,3}
(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)
SELECT array_position(ARRAY[1,2,3,4,5], 4);
array_position
----------------
4
(1 row)
SELECT array_position(ARRAY[5,3,4,2,1], 4);
array_position
----------------
3
(1 row)
SELECT array_position(ARRAY[[1,2],[3,4]], 3);
ERROR: searching for elements in multidimensional arrays is not supported
SELECT array_position(ARRAY['sun','mon','tue','wed','thu','fri','sat'], 'mon');
array_position
----------------
2
(1 row)
SELECT array_position(ARRAY['sun','mon','tue','wed','thu','fri','sat'], 'sat');
array_position
----------------
7
(1 row)
SELECT array_position(ARRAY['sun','mon','tue','wed','thu','fri','sat'], NULL);
array_position
----------------
(1 row)
SELECT array_position(ARRAY['sun','mon','tue','wed','thu',NULL,'fri','sat'], NULL);
array_position
----------------
6
(1 row)
SELECT array_position(ARRAY['sun','mon','tue','wed','thu',NULL,'fri','sat'], 'sat');
array_position
----------------
8
(1 row)
SELECT array_positions(NULL, 10);
array_positions
-----------------
(1 row)
SELECT array_positions(NULL, NULL::int);
array_positions
-----------------
(1 row)
SELECT array_positions(ARRAY[1,2,3,4,5,6,1,2,3,4,5,6], 4);
array_positions
-----------------
{4,10}
(1 row)
SELECT array_positions(ARRAY[[1,2],[3,4]], 4);
ERROR: searching for elements in multidimensional arrays is not supported
SELECT array_positions(ARRAY[1,2,3,4,5,6,1,2,3,4,5,6], NULL);
array_positions
-----------------
{}
(1 row)
SELECT array_positions(ARRAY[1,2,3,NULL,5,6,1,2,3,NULL,5,6], NULL);
array_positions
-----------------
{4,10}
(1 row)
SELECT array_length(array_positions(ARRAY(SELECT 'AAAAAAAAAAAAAAAAAAAAAAAAA'::text || i % 10
FROM generate_series(1,100) g(i)),
'AAAAAAAAAAAAAAAAAAAAAAAAA5'), 1);
array_length
--------------
10
(1 row)
DO $$
DECLARE
o int;
a int[] := ARRAY[1,2,3,2,3,1,2];
BEGIN
o := array_position(a, 2);
WHILE o IS NOT NULL
LOOP
RAISE NOTICE '%', o;
o := array_position(a, 2, o + 1);
END LOOP;
END
$$ LANGUAGE plpgsql;
NOTICE: 2
NOTICE: 4
NOTICE: 7
SELECT array_position('[2:4]={1,2,3}'::int[], 1);
array_position
----------------
2
(1 row)
SELECT array_positions('[2:4]={1,2,3}'::int[], 1);
array_positions
-----------------
{2}
(1 row)
SELECT
array_position(ids, (1, 1)),
array_positions(ids, (1, 1))
FROM
(VALUES
(ARRAY[(0, 0), (1, 1)]),
(ARRAY[(1, 1)])
) AS f (ids);
array_position | array_positions
----------------+-----------------
2 | {2}
1 | {1}
(2 rows)
-- 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)
SELECT ARRAY[1.1] || ARRAY[2,3,4];
?column?
-------------
{1.1,2,3,4}
(1 row)
SELECT array_agg(x) || array_agg(x) FROM (VALUES (ROW(1,2)), (ROW(3,4))) v(x);
?column?
-----------------------------------
{"(1,2)","(3,4)","(1,2)","(3,4)"}
(1 row)
SELECT ROW(1,2) || array_agg(x) FROM (VALUES (ROW(3,4)), (ROW(5,6))) v(x);
?column?
---------------------------
{"(1,2)","(3,4)","(5,6)"}
(1 row)
SELECT * FROM array_op_test WHERE i @> '{32}' ORDER BY seqno;
seqno | i | t
-------+---------------------------------+------------------------------------------------------------------------------------------------------------------------------------
6 | {39,35,5,94,17,92,60,32} | {AAAAAAAAAAAAAAA35875,AAAAAAAAAAAAAAAA23657}
74 | {32} | {AAAAAAAAAAAAAAAA1729,AAAAAAAAAAAAA22860,AAAAAA99807,AAAAA17383,AAAAAAAAAAAAAAA67062,AAAAAAAAAAA15165,AAAAAAAAAAA50956}
77 | {97,15,32,17,55,59,18,37,50,39} | {AAAAAAAAAAAA67946,AAAAAA54032,AAAAAAAA81587,55847,AAAAAAAAAAAAAA28620,AAAAAAAAAAAAAAAAA43052,AAAAAA75463,AAAA49534,AAAAAAAA44066}
89 | {40,32,17,6,30,88} | {AA44673,AAAAAAAAAAA6119,AAAAAAAAAAAAAAAA23657,AAAAAAAAAAAAAAAAAA47955,AAAAAAAAAAAAAAAA33598,AAAAAAAAAAA33576,AA44673}
98 | {38,34,32,89} | {AAAAAAAAAAAAAAAAAA71621,AAAA8857,AAAAAAAAAAAAAAAAAAA65037,AAAAAAAAAAAAAAAA31334,AAAAAAAAAA48845}
100 | {85,32,57,39,49,84,32,3,30} | {AAAAAAA80240,AAAAAAAAAAAAAAAA1729,AAAAA60038,AAAAAAAAAAA92631,AAAAAAAA9523}
(6 rows)
SELECT * FROM array_op_test WHERE i && '{32}' ORDER BY seqno;
seqno | i | t
-------+---------------------------------+------------------------------------------------------------------------------------------------------------------------------------
6 | {39,35,5,94,17,92,60,32} | {AAAAAAAAAAAAAAA35875,AAAAAAAAAAAAAAAA23657}
74 | {32} | {AAAAAAAAAAAAAAAA1729,AAAAAAAAAAAAA22860,AAAAAA99807,AAAAA17383,AAAAAAAAAAAAAAA67062,AAAAAAAAAAA15165,AAAAAAAAAAA50956}
77 | {97,15,32,17,55,59,18,37,50,39} | {AAAAAAAAAAAA67946,AAAAAA54032,AAAAAAAA81587,55847,AAAAAAAAAAAAAA28620,AAAAAAAAAAAAAAAAA43052,AAAAAA75463,AAAA49534,AAAAAAAA44066}
89 | {40,32,17,6,30,88} | {AA44673,AAAAAAAAAAA6119,AAAAAAAAAAAAAAAA23657,AAAAAAAAAAAAAAAAAA47955,AAAAAAAAAAAAAAAA33598,AAAAAAAAAAA33576,AA44673}
98 | {38,34,32,89} | {AAAAAAAAAAAAAAAAAA71621,AAAA8857,AAAAAAAAAAAAAAAAAAA65037,AAAAAAAAAAAAAAAA31334,AAAAAAAAAA48845}
100 | {85,32,57,39,49,84,32,3,30} | {AAAAAAA80240,AAAAAAAAAAAAAAAA1729,AAAAA60038,AAAAAAAAAAA92631,AAAAAAAA9523}
(6 rows)
SELECT * FROM array_op_test WHERE i @> '{17}' ORDER BY seqno;
seqno | i | t
-------+---------------------------------+------------------------------------------------------------------------------------------------------------------------------------
6 | {39,35,5,94,17,92,60,32} | {AAAAAAAAAAAAAAA35875,AAAAAAAAAAAAAAAA23657}
12 | {17,99,18,52,91,72,0,43,96,23} | {AAAAA33250,AAAAAAAAAAAAAAAAAAA85420,AAAAAAAAAAA33576}
15 | {17,14,16,63,67} | {AA6416,AAAAAAAAAA646,AAAAA95309}
19 | {52,82,17,74,23,46,69,51,75} | {AAAAAAAAAAAAA73084,AAAAA75968,AAAAAAAAAAAAAAAA14047,AAAAAAA80240,AAAAAAAAAAAAAAAAAAA1205,A68938}
53 | {38,17} | {AAAAAAAAAAA21658}
65 | {61,5,76,59,17} | {AAAAAA99807,AAAAA64741,AAAAAAAAAAA53908,AA21643,AAAAAAAAA10012}
77 | {97,15,32,17,55,59,18,37,50,39} | {AAAAAAAAAAAA67946,AAAAAA54032,AAAAAAAA81587,55847,AAAAAAAAAAAAAA28620,AAAAAAAAAAAAAAAAA43052,AAAAAA75463,AAAA49534,AAAAAAAA44066}
89 | {40,32,17,6,30,88} | {AA44673,AAAAAAAAAAA6119,AAAAAAAAAAAAAAAA23657,AAAAAAAAAAAAAAAAAA47955,AAAAAAAAAAAAAAAA33598,AAAAAAAAAAA33576,AA44673}
(8 rows)
SELECT * FROM array_op_test WHERE i && '{17}' ORDER BY seqno;
seqno | i | t
-------+---------------------------------+------------------------------------------------------------------------------------------------------------------------------------
6 | {39,35,5,94,17,92,60,32} | {AAAAAAAAAAAAAAA35875,AAAAAAAAAAAAAAAA23657}
12 | {17,99,18,52,91,72,0,43,96,23} | {AAAAA33250,AAAAAAAAAAAAAAAAAAA85420,AAAAAAAAAAA33576}
15 | {17,14,16,63,67} | {AA6416,AAAAAAAAAA646,AAAAA95309}
19 | {52,82,17,74,23,46,69,51,75} | {AAAAAAAAAAAAA73084,AAAAA75968,AAAAAAAAAAAAAAAA14047,AAAAAAA80240,AAAAAAAAAAAAAAAAAAA1205,A68938}
53 | {38,17} | {AAAAAAAAAAA21658}
65 | {61,5,76,59,17} | {AAAAAA99807,AAAAA64741,AAAAAAAAAAA53908,AA21643,AAAAAAAAA10012}
77 | {97,15,32,17,55,59,18,37,50,39} | {AAAAAAAAAAAA67946,AAAAAA54032,AAAAAAAA81587,55847,AAAAAAAAAAAAAA28620,AAAAAAAAAAAAAAAAA43052,AAAAAA75463,AAAA49534,AAAAAAAA44066}
89 | {40,32,17,6,30,88} | {AA44673,AAAAAAAAAAA6119,AAAAAAAAAAAAAAAA23657,AAAAAAAAAAAAAAAAAA47955,AAAAAAAAAAAAAAAA33598,AAAAAAAAAAA33576,AA44673}
(8 rows)
SELECT * FROM array_op_test WHERE i @> '{32,17}' ORDER BY seqno;
seqno | i | t
-------+---------------------------------+------------------------------------------------------------------------------------------------------------------------------------
6 | {39,35,5,94,17,92,60,32} | {AAAAAAAAAAAAAAA35875,AAAAAAAAAAAAAAAA23657}
77 | {97,15,32,17,55,59,18,37,50,39} | {AAAAAAAAAAAA67946,AAAAAA54032,AAAAAAAA81587,55847,AAAAAAAAAAAAAA28620,AAAAAAAAAAAAAAAAA43052,AAAAAA75463,AAAA49534,AAAAAAAA44066}
89 | {40,32,17,6,30,88} | {AA44673,AAAAAAAAAAA6119,AAAAAAAAAAAAAAAA23657,AAAAAAAAAAAAAAAAAA47955,AAAAAAAAAAAAAAAA33598,AAAAAAAAAAA33576,AA44673}
(3 rows)
SELECT * FROM array_op_test WHERE i && '{32,17}' ORDER BY seqno;
seqno | i | t
-------+---------------------------------+------------------------------------------------------------------------------------------------------------------------------------
6 | {39,35,5,94,17,92,60,32} | {AAAAAAAAAAAAAAA35875,AAAAAAAAAAAAAAAA23657}
12 | {17,99,18,52,91,72,0,43,96,23} | {AAAAA33250,AAAAAAAAAAAAAAAAAAA85420,AAAAAAAAAAA33576}
15 | {17,14,16,63,67} | {AA6416,AAAAAAAAAA646,AAAAA95309}
19 | {52,82,17,74,23,46,69,51,75} | {AAAAAAAAAAAAA73084,AAAAA75968,AAAAAAAAAAAAAAAA14047,AAAAAAA80240,AAAAAAAAAAAAAAAAAAA1205,A68938}
53 | {38,17} | {AAAAAAAAAAA21658}
65 | {61,5,76,59,17} | {AAAAAA99807,AAAAA64741,AAAAAAAAAAA53908,AA21643,AAAAAAAAA10012}
74 | {32} | {AAAAAAAAAAAAAAAA1729,AAAAAAAAAAAAA22860,AAAAAA99807,AAAAA17383,AAAAAAAAAAAAAAA67062,AAAAAAAAAAA15165,AAAAAAAAAAA50956}
77 | {97,15,32,17,55,59,18,37,50,39} | {AAAAAAAAAAAA67946,AAAAAA54032,AAAAAAAA81587,55847,AAAAAAAAAAAAAA28620,AAAAAAAAAAAAAAAAA43052,AAAAAA75463,AAAA49534,AAAAAAAA44066}
89 | {40,32,17,6,30,88} | {AA44673,AAAAAAAAAAA6119,AAAAAAAAAAAAAAAA23657,AAAAAAAAAAAAAAAAAA47955,AAAAAAAAAAAAAAAA33598,AAAAAAAAAAA33576,AA44673}
98 | {38,34,32,89} | {AAAAAAAAAAAAAAAAAA71621,AAAA8857,AAAAAAAAAAAAAAAAAAA65037,AAAAAAAAAAAAAAAA31334,AAAAAAAAAA48845}
100 | {85,32,57,39,49,84,32,3,30} | {AAAAAAA80240,AAAAAAAAAAAAAAAA1729,AAAAA60038,AAAAAAAAAAA92631,AAAAAAAA9523}
(11 rows)
SELECT * FROM array_op_test WHERE i <@ '{38,34,32,89}' ORDER BY seqno;
seqno | i | t
-------+---------------+----------------------------------------------------------------------------------------------------------------------------
40 | {34} | {AAAAAAAAAAAAAA10611,AAAAAAAAAAAAAAAAAAA1205,AAAAAAAAAAA50956,AAAAAAAAAAAAAAAA31334,AAAAA70466,AAAAAAAA81587,AAAAAAA74623}
74 | {32} | {AAAAAAAAAAAAAAAA1729,AAAAAAAAAAAAA22860,AAAAAA99807,AAAAA17383,AAAAAAAAAAAAAAA67062,AAAAAAAAAAA15165,AAAAAAAAAAA50956}
98 | {38,34,32,89} | {AAAAAAAAAAAAAAAAAA71621,AAAA8857,AAAAAAAAAAAAAAAAAAA65037,AAAAAAAAAAAAAAAA31334,AAAAAAAAAA48845}
101 | {} | {}
(4 rows)
SELECT * FROM array_op_test WHERE i = '{}' ORDER BY seqno;
seqno | i | t
-------+----+----
101 | {} | {}
(1 row)
SELECT * FROM array_op_test WHERE i @> '{}' ORDER BY seqno;
seqno | i | t
-------+---------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 | {92,75,71,52,64,83} | {AAAAAAAA44066,AAAAAA1059,AAAAAAAAAAA176,AAAAAAA48038}
2 | {3,6} | {AAAAAA98232,AAAAAAAA79710,AAAAAAAAAAAAAAAAA69675,AAAAAAAAAAAAAAAA55798,AAAAAAAAA12793}
3 | {37,64,95,43,3,41,13,30,11,43} | {AAAAAAAAAA48845,AAAAA75968,AAAAA95309,AAA54451,AAAAAAAAAA22292,AAAAAAA99836,A96617,AA17009,AAAAAAAAAAAAAA95246}
4 | {71,39,99,55,33,75,45} | {AAAAAAAAA53663,AAAAAAAAAAAAAAA67062,AAAAAAAAAA64777,AAA99043,AAAAAAAAAAAAAAAAAAA91804,39557}
5 | {50,42,77,50,4} | {AAAAAAAAAAAAAAAAA26540,AAAAAAA79710,AAAAAAAAAAAAAAAAAAA1205,AAAAAAAAAAA176,AAAAA95309,AAAAAAAAAAA46154,AAAAAA66777,AAAAAAAAA27249,AAAAAAAAAA64777,AAAAAAAAAAAAAAAAAAA70104}
6 | {39,35,5,94,17,92,60,32} | {AAAAAAAAAAAAAAA35875,AAAAAAAAAAAAAAAA23657}
7 | {12,51,88,64,8} | {AAAAAAAAAAAAAAAAAA12591,AAAAAAAAAAAAAAAAA50407,AAAAAAAAAAAA67946}
8 | {60,84} | {AAAAAAA81898,AAAAAA1059,AAAAAAAAAAAA81511,AAAAA961,AAAAAAAAAAAAAAAA31334,AAAAA64741,AA6416,AAAAAAAAAAAAAAAAAA32918,AAAAAAAAAAAAAAAAA50407}
9 | {56,52,35,27,80,44,81,22} | {AAAAAAAAAAAAAAA73034,AAAAAAAAAAAAA7929,AAAAAAA66161,AA88409,39557,A27153,AAAAAAAA9523,AAAAAAAAAAA99000}
10 | {71,5,45} | {AAAAAAAAAAA21658,AAAAAAAAAAAA21089,AAA54451,AAAAAAAAAAAAAAAAAA54141,AAAAAAAAAAAAAA28620,AAAAAAAAAAA21658,AAAAAAAAAAA74076,AAAAAAAAA27249}
11 | {41,86,74,48,22,74,47,50} | {AAAAAAAA9523,AAAAAAAAAAAA37562,AAAAAAAAAAAAAAAA14047,AAAAAAAAAAA46154,AAAA41702,AAAAAAAAAAAAAAAAA764,AAAAA62737,39557}
12 | {17,99,18,52,91,72,0,43,96,23} | {AAAAA33250,AAAAAAAAAAAAAAAAAAA85420,AAAAAAAAAAA33576}
13 | {3,52,34,23} | {AAAAAA98232,AAAA49534,AAAAAAAAAAA21658}
14 | {78,57,19} | {AAAA8857,AAAAAAAAAAAAAAA73034,AAAAAAAA81587,AAAAAAAAAAAAAAA68526,AAAAA75968,AAAAAAAAAAAAAA65909,AAAAAAAAA10012,AAAAAAAAAAAAAA65909}
15 | {17,14,16,63,67} | {AA6416,AAAAAAAAAA646,AAAAA95309}
16 | {14,63,85,11} | {AAAAAA66777}
17 | {7,10,81,85} | {AAAAAA43678,AAAAAAA12144,AAAAAAAAAAA50956,AAAAAAAAAAAAAAAAAAA15356}
18 | {1} | {AAAAAAAAAAA33576,AAAAA95309,64261,AAA59323,AAAAAAAAAAAAAA95246,55847,AAAAAAAAAAAA67946,AAAAAAAAAAAAAAAAAA64374}
19 | {52,82,17,74,23,46,69,51,75} | {AAAAAAAAAAAAA73084,AAAAA75968,AAAAAAAAAAAAAAAA14047,AAAAAAA80240,AAAAAAAAAAAAAAAAAAA1205,A68938}
20 | {72,89,70,51,54,37,8,49,79} | {AAAAAA58494}
21 | {2,8,65,10,5,79,43} | {AAAAAAAAAAAAAAAAA88852,AAAAAAAAAAAAAAAAAAA91804,AAAAA64669,AAAAAAAAAAAAAAAA1443,AAAAAAAAAAAAAAAA23657,AAAAA12179,AAAAAAAAAAAAAAAAA88852,AAAAAAAAAAAAAAAA31334,AAAAAAAAAAAAAAAA41303,AAAAAAAAAAAAAAAAAAA85420}
22 | {11,6,56,62,53,30} | {AAAAAAAA72908}
23 | {40,90,5,38,72,40,30,10,43,55} | {A6053,AAAAAAAAAAA6119,AA44673,AAAAAAAAAAAAAAAAA764,AA17009,AAAAA17383,AAAAA70514,AAAAA33250,AAAAA95309,AAAAAAAAAAAA37562}
24 | {94,61,99,35,48} | {AAAAAAAAAAA50956,AAAAAAAAAAA15165,AAAA85070,AAAAAAAAAAAAAAA36627,AAAAA961,AAAAAAAAAA55219}
25 | {31,1,10,11,27,79,38} | {AAAAAAAAAAAAAAAAAA59334,45449}
26 | {71,10,9,69,75} | {47735,AAAAAAA21462,AAAAAAAAAAAAAAAAA6897,AAAAAAAAAAAAAAAAAAA91804,AAAAAAAAA72121,AAAAAAAAAAAAAAAAAAA1205,AAAAA41597,AAAA8857,AAAAAAAAAAAAAAAAAAA15356,AA17009}
27 | {94} | {AA6416,A6053,AAAAAAA21462,AAAAAAA57334,AAAAAAAAAAAAAAAAAA12591,AA88409,AAAAAAAAAAAAA70254}
28 | {14,33,6,34,14} | {AAAAAAAAAAAAAAA13198,AAAAAAAA69452,AAAAAAAAAAA82945,AAAAAAA12144,AAAAAAAAA72121,AAAAAAAAAA18601}
29 | {39,21} | {AAAAAAAAAAAAAAAAA6897,AAAAAAAAAAAAAAAAAAA38885,AAAA85070,AAAAAAAAAAAAAAAAAAA70104,AAAAA66674,AAAAAAAAAAAAA62007,AAAAAAAA69452,AAAAAAA1242,AAAAAAAAAAAAAAAA1729,AAAA35194}
30 | {26,81,47,91,34} | {AAAAAAAAAAAAAAAAAAA70104,AAAAAAA80240}
31 | {80,24,18,21,54} | {AAAAAAAAAAAAAAA13198,AAAAAAAAAAAAAAAAAAA70415,A27153,AAAAAAAAA53663,AAAAAAAAAAAAAAAAA50407,A68938}
32 | {58,79,82,80,67,75,98,10,41} | {AAAAAAAAAAAAAAAAAA61286,AAA54451,AAAAAAAAAAAAAAAAAAA87527,A96617,51533}
33 | {74,73} | {A85417,AAAAAAA56483,AAAAA17383,AAAAAAAAAAAAA62159,AAAAAAAAAAAA52814,AAAAAAAAAAAAA85723,AAAAAAAAAAAAAAAAAA55796}
34 | {70,45} | {AAAAAAAAAAAAAAAAAA71621,AAAAAAAAAAAAAA28620,AAAAAAAAAA55219,AAAAAAAA23648,AAAAAAAAAA22292,AAAAAAA1242}
35 | {23,40} | {AAAAAAAAAAAA52814,AAAA48949,AAAAAAAAA34727,AAAA8857,AAAAAAAAAAAAAAAAAAA62179,AAAAAAAAAAAAAAA68526,AAAAAAA99836,AAAAAAAA50094,AAAA91194,AAAAAAAAAAAAA73084}
36 | {79,82,14,52,30,5,79} | {AAAAAAAAA53663,AAAAAAAAAAAAAAAA55798,AAAAAAAAAAAAAAAAAAA89194,AA88409,AAAAAAAAAAAAAAA81326,AAAAAAAAAAAAAAAAA63050,AAAAAAAAAAAAAAAA33598}
37 | {53,11,81,39,3,78,58,64,74} | {AAAAAAAAAAAAAAAAAAA17075,AAAAAAA66161,AAAAAAAA23648,AAAAAAAAAAAAAA10611}
38 | {59,5,4,95,28} | {AAAAAAAAAAA82945,A96617,47735,AAAAA12179,AAAAA64669,AAAAAA99807,AA74433,AAAAAAAAAAAAAAAAA59387}
39 | {82,43,99,16,74} | {AAAAAAAAAAAAAAA67062,AAAAAAA57334,AAAAAAAAAAAAAA65909,A27153,AAAAAAAAAAAAAAAAAAA17075,AAAAAAAAAAAAAAAAA43052,AAAAAAAAAA64777,AAAAAAAAAAAA81511,AAAAAAAAAAAAAA65909,AAAAAAAAAAAAAA28620}
40 | {34} | {AAAAAAAAAAAAAA10611,AAAAAAAAAAAAAAAAAAA1205,AAAAAAAAAAA50956,AAAAAAAAAAAAAAAA31334,AAAAA70466,AAAAAAAA81587,AAAAAAA74623}
41 | {19,26,63,12,93,73,27,94} | {AAAAAAA79710,AAAAAAAAAA55219,AAAA41702,AAAAAAAAAAAAAAAAAAA17075,AAAAAAAAAAAAAAAAAA71621,AAAAAAAAAAAAAAAAA63050,AAAAAAA99836,AAAAAAAAAAAAAA8666}
42 | {15,76,82,75,8,91} | {AAAAAAAAAAA176,AAAAAA38063,45449,AAAAAA54032,AAAAAAA81898,AA6416,AAAAAAAAAAAAAAAAAAA62179,45449,AAAAA60038,AAAAAAAA81587}
43 | {39,87,91,97,79,28} | {AAAAAAAAAAA74076,A96617,AAAAAAAAAAAAAAAAAAA89194,AAAAAAAAAAAAAAAAAA55796,AAAAAAAAAAAAAAAA23657,AAAAAAAAAAAA67946}
44 | {40,58,68,29,54} | {AAAAAAA81898,AAAAAA66777,AAAAAA98232}
45 | {99,45} | {AAAAAAAA72908,AAAAAAAAAAAAAAAAAAA17075,AA88409,AAAAAAAAAAAAAAAAAA36842,AAAAAAA48038,AAAAAAAAAAAAAA10611}
46 | {53,24} | {AAAAAAAAAAA53908,AAAAAA54032,AAAAA17383,AAAA48949,AAAAAAAAAA18601,AAAAA64669,45449,AAAAAAAAAAA98051,AAAAAAAAAAAAAAAAAA71621}
47 | {98,23,64,12,75,61} | {AAA59323,AAAAA95309,AAAAAAAAAAAAAAAA31334,AAAAAAAAA27249,AAAAA17383,AAAAAAAAAAAA37562,AAAAAA1059,A84822,55847,AAAAA70466}
48 | {76,14} | {AAAAAAAAAAAAA59671,AAAAAAAAAAAAAAAAAAA91804,AAAAAA66777,AAAAAAAAAAAAAAAAAAA89194,AAAAAAAAAAAAAAA36627,AAAAAAAAAAAAAAAAAAA17075,AAAAAAAAAAAAA73084,AAAAAAA79710,AAAAAAAAAAAAAAA40402,AAAAAAAAAAAAAAAAAAA65037}
49 | {56,5,54,37,49} | {AA21643,AAAAAAAAAAA92631,AAAAAAAA81587}
50 | {20,12,37,64,93} | {AAAAAAAAAA5483,AAAAAAAAAAAAAAAAAAA1205,AA6416,AAAAAAAAAAAAAAAAA63050,AAAAAAAAAAAAAAAAAA47955}
51 | {47} | {AAAAAAAAAAAAAA96505,AAAAAAAAAAAAAAAAAA36842,AAAAA95309,AAAAAAAA81587,AA6416,AAAA91194,AAAAAA58494,AAAAAA1059,AAAAAAAA69452}
52 | {89,0} | {AAAAAAAAAAAAAAAAAA47955,AAAAAAA48038,AAAAAAAAAAAAAAAAA43052,AAAAAAAAAAAAA73084,AAAAA70466,AAAAAAAAAAAAAAAAA764,AAAAAAAAAAA46154,AA66862}
53 | {38,17} | {AAAAAAAAAAA21658}
54 | {70,47} | {AAAAAAAAAAAAAAAAAA54141,AAAAA40681,AAAAAAA48038,AAAAAAAAAAAAAAAA29150,AAAAA41597,AAAAAAAAAAAAAAAAAA59334,AA15322}
55 | {47,79,47,64,72,25,71,24,93} | {AAAAAAAAAAAAAAAAAA55796,AAAAA62737}
56 | {33,7,60,54,93,90,77,85,39} | {AAAAAAAAAAAAAAAAAA32918,AA42406}
57 | {23,45,10,42,36,21,9,96} | {AAAAAAAAAAAAAAAAAAA70415}
58 | {92} | {AAAAAAAAAAAAAAAA98414,AAAAAAAA23648,AAAAAAAAAAAAAAAAAA55796,AA25381,AAAAAAAAAAA6119}
59 | {9,69,46,77} | {39557,AAAAAAA89932,AAAAAAAAAAAAAAAAA43052,AAAAAAAAAAAAAAAAA26540,AAA20874,AA6416,AAAAAAAAAAAAAAAAAA47955}
60 | {62,2,59,38,89} | {AAAAAAA89932,AAAAAAAAAAAAAAAAAAA15356,AA99927,AA17009,AAAAAAAAAAAAAAA35875}
61 | {72,2,44,95,54,54,13} | {AAAAAAAAAAAAAAAAAAA91804}
62 | {83,72,29,73} | {AAAAAAAAAAAAA15097,AAAA8857,AAAAAAAAAAAA35809,AAAAAAAAAAAA52814,AAAAAAAAAAAAAAAAAAA38885,AAAAAAAAAAAAAAAAAA24183,AAAAAA43678,A96617}
63 | {11,4,61,87} | {AAAAAAAAA27249,AAAAAAAAAAAAAAAAAA32918,AAAAAAAAAAAAAAA13198,AAA20874,39557,51533,AAAAAAAAAAA53908,AAAAAAAAAAAAAA96505,AAAAAAAA78938}
64 | {26,19,34,24,81,78} | {A96617,AAAAAAAAAAAAAAAAAAA70104,A68938,AAAAAAAAAAA53908,AAAAAAAAAAAAAAA453,AA17009,AAAAAAA80240}
65 | {61,5,76,59,17} | {AAAAAA99807,AAAAA64741,AAAAAAAAAAA53908,AA21643,AAAAAAAAA10012}
66 | {31,23,70,52,4,33,48,25} | {AAAAAAAAAAAAAAAAA69675,AAAAAAAA50094,AAAAAAAAAAA92631,AAAA35194,39557,AAAAAAA99836}
67 | {31,94,7,10} | {AAAAAA38063,A96617,AAAA35194,AAAAAAAAAAAA67946}
68 | {90,43,38} | {AA75092,AAAAAAAAAAAAAAAAA69675,AAAAAAAAAAA92631,AAAAAAAAA10012,AAAAAAAAAAAAA7929,AA21643}
69 | {67,35,99,85,72,86,44} | {AAAAAAAAAAAAAAAAAAA1205,AAAAAAAA50094,AAAAAAAAAAAAAAAA1729,AAAAAAAAAAAAAAAAAA47955}
70 | {56,70,83} | {AAAA41702,AAAAAAAAAAA82945,AA21643,AAAAAAAAAAA99000,A27153,AA25381,AAAAAAAAAAAAAA96505,AAAAAAA1242}
71 | {74,26} | {AAAAAAAAAAA50956,AA74433,AAAAAAA21462,AAAAAAAAAAAAAAAAAAA17075,AAAAAAAAAAAAAAA36627,AAAAAAAAAAAAA70254,AAAAAAAAAA43419,39557}
72 | {22,1,16,78,20,91,83} | {47735,AAAAAAA56483,AAAAAAAAAAAAA93788,AA42406,AAAAAAAAAAAAA73084,AAAAAAAA72908,AAAAAAAAAAAAAAAAAA61286,AAAAA66674,AAAAAAAAAAAAAAAAA50407}
73 | {88,25,96,78,65,15,29,19} | {AAA54451,AAAAAAAAA27249,AAAAAAA9228,AAAAAAAAAAAAAAA67062,AAAAAAAAAAAAAAAAAAA70415,AAAAA17383,AAAAAAAAAAAAAAAA33598}
74 | {32} | {AAAAAAAAAAAAAAAA1729,AAAAAAAAAAAAA22860,AAAAAA99807,AAAAA17383,AAAAAAAAAAAAAAA67062,AAAAAAAAAAA15165,AAAAAAAAAAA50956}
75 | {12,96,83,24,71,89,55} | {AAAA48949,AAAAAAAA29716,AAAAAAAAAAAAAAAAAAA1205,AAAAAAAAAAAA67946,AAAAAAAAAAAAAAAA29150,AAA28075,AAAAAAAAAAAAAAAAA43052}
76 | {92,55,10,7} | {AAAAAAAAAAAAAAA67062}
77 | {97,15,32,17,55,59,18,37,50,39} | {AAAAAAAAAAAA67946,AAAAAA54032,AAAAAAAA81587,55847,AAAAAAAAAAAAAA28620,AAAAAAAAAAAAAAAAA43052,AAAAAA75463,AAAA49534,AAAAAAAA44066}
78 | {55,89,44,84,34} | {AAAAAAAAAAA6119,AAAAAAAAAAAAAA8666,AA99927,AA42406,AAAAAAA81898,AAAAAAA9228,AAAAAAAAAAA92631,AA21643,AAAAAAAAAAAAAA28620}
79 | {45} | {AAAAAAAAAA646,AAAAAAAAAAAAAAAAAAA70415,AAAAAA43678,AAAAAAAA72908}
80 | {74,89,44,80,0} | {AAAA35194,AAAAAAAA79710,AAA20874,AAAAAAAAAAAAAAAAAAA70104,AAAAAAAAAAAAA73084,AAAAAAA57334,AAAAAAA9228,AAAAAAAAAAAAA62007}
81 | {63,77,54,48,61,53,97} | {AAAAAAAAAAAAAAA81326,AAAAAAAAAA22292,AA25381,AAAAAAAAAAA74076,AAAAAAA81898,AAAAAAAAA72121}
82 | {34,60,4,79,78,16,86,89,42,50} | {AAAAA40681,AAAAAAAAAAAAAAAAAA12591,AAAAAAA80240,AAAAAAAAAAAAAAAA55798,AAAAAAAAAAAAAAAAAAA70104}
83 | {14,10} | {AAAAAAAAAA22292,AAAAAAAAAAAAA70254,AAAAAAAAAAA6119}
84 | {11,83,35,13,96,94} | {AAAAA95309,AAAAAAAAAAAAAAAAAA32918,AAAAAAAAAAAAAAAAAA24183}
85 | {39,60} | {AAAAAAAAAAAAAAAA55798,AAAAAAAAAA22292,AAAAAAA66161,AAAAAAA21462,AAAAAAAAAAAAAAAAAA12591,55847,AAAAAA98232,AAAAAAAAAAA46154}
86 | {33,81,72,74,45,36,82} | {AAAAAAAA81587,AAAAAAAAAAAAAA96505,45449,AAAA80176}
87 | {57,27,50,12,97,68} | {AAAAAAAAAAAAAAAAA26540,AAAAAAAAA10012,AAAAAAAAAAAA35809,AAAAAAAAAAAAAAAA29150,AAAAAAAAAAA82945,AAAAAA66777,31228,AAAAAAAAAAAAAAAA23657,AAAAAAAAAAAAAA28620,AAAAAAAAAAAAAA96505}
88 | {41,90,77,24,6,24} | {AAAA35194,AAAA35194,AAAAAAA80240,AAAAAAAAAAA46154,AAAAAA58494,AAAAAAAAAAAAAAAAAAA17075,AAAAAAAAAAAAAAAAAA59334,AAAAAAAAAAAAAAAAAAA91804,AA74433}
89 | {40,32,17,6,30,88} | {AA44673,AAAAAAAAAAA6119,AAAAAAAAAAAAAAAA23657,AAAAAAAAAAAAAAAAAA47955,AAAAAAAAAAAAAAAA33598,AAAAAAAAAAA33576,AA44673}
90 | {88,75} | {AAAAA60038,AAAAAAAA23648,AAAAAAAAAAA99000,AAAA41702,AAAAAAAAAAAAA22860,AAAAAAAAAAAAAAA68526}
91 | {78} | {AAAAAAAAAAAAA62007,AAA99043}
92 | {85,63,49,45} | {AAAAAAA89932,AAAAAAAAAAAAA22860,AAAAAAAAAAAAAAAAAAA1205,AAAAAAAAAAAA21089}
93 | {11} | {AAAAAAAAAAA176,AAAAAAAAAAAAAA8666,AAAAAAAAAAAAAAA453,AAAAAAAAAAAAA85723,A68938,AAAAAAAAAAAAA9821,AAAAAAA48038,AAAAAAAAAAAAAAAAA59387,AA99927,AAAAA17383}
94 | {98,9,85,62,88,91,60,61,38,86} | {AAAAAAAA81587,AAAAA17383,AAAAAAAA81587}
95 | {47,77} | {AAAAAAAAAAAAAAAAA764,AAAAAAAAAAA74076,AAAAAAAAAA18107,AAAAA40681,AAAAAAAAAAAAAAA35875,AAAAA60038,AAAAAAA56483}
96 | {23,97,43} | {AAAAAAAAAA646,A87088}
97 | {54,2,86,65} | {47735,AAAAAAA99836,AAAAAAAAAAAAAAAAA6897,AAAAAAAAAAAAAAAA29150,AAAAAAA80240,AAAAAAAAAAAAAAAA98414,AAAAAAA56483,AAAAAAAAAAAAAAAA29150,AAAAAAA39692,AA21643}
98 | {38,34,32,89} | {AAAAAAAAAAAAAAAAAA71621,AAAA8857,AAAAAAAAAAAAAAAAAAA65037,AAAAAAAAAAAAAAAA31334,AAAAAAAAAA48845}
99 | {37,86} | {AAAAAAAAAAAAAAAAAA32918,AAAAA70514,AAAAAAAAA10012,AAAAAAAAAAAAAAAAA59387,AAAAAAAAAA64777,AAAAAAAAAAAAAAAAAAA15356}
100 | {85,32,57,39,49,84,32,3,30} | {AAAAAAA80240,AAAAAAAAAAAAAAAA1729,AAAAA60038,AAAAAAAAAAA92631,AAAAAAAA9523}
101 | {} | {}
102 | {NULL} | {NULL}
(102 rows)
SELECT * FROM array_op_test WHERE i && '{}' ORDER BY seqno;
seqno | i | t
-------+---+---
(0 rows)
SELECT * FROM array_op_test WHERE i <@ '{}' ORDER BY seqno;
seqno | i | t
-------+----+----
101 | {} | {}
(1 row)
SELECT * FROM array_op_test WHERE i = '{NULL}' ORDER BY seqno;
seqno | i | t
-------+--------+--------
102 | {NULL} | {NULL}
(1 row)
SELECT * FROM array_op_test WHERE i @> '{NULL}' ORDER BY seqno;
seqno | i | t
-------+---+---
(0 rows)
SELECT * FROM array_op_test WHERE i && '{NULL}' ORDER BY seqno;
seqno | i | t
-------+---+---
(0 rows)
SELECT * FROM array_op_test WHERE i <@ '{NULL}' ORDER BY seqno;
seqno | i | t
-------+----+----
101 | {} | {}
(1 row)
SELECT * FROM array_op_test WHERE t @> '{AAAAAAAA72908}' ORDER BY seqno;
seqno | i | t
-------+-----------------------+--------------------------------------------------------------------------------------------------------------------------------------------
22 | {11,6,56,62,53,30} | {AAAAAAAA72908}
45 | {99,45} | {AAAAAAAA72908,AAAAAAAAAAAAAAAAAAA17075,AA88409,AAAAAAAAAAAAAAAAAA36842,AAAAAAA48038,AAAAAAAAAAAAAA10611}
72 | {22,1,16,78,20,91,83} | {47735,AAAAAAA56483,AAAAAAAAAAAAA93788,AA42406,AAAAAAAAAAAAA73084,AAAAAAAA72908,AAAAAAAAAAAAAAAAAA61286,AAAAA66674,AAAAAAAAAAAAAAAAA50407}
79 | {45} | {AAAAAAAAAA646,AAAAAAAAAAAAAAAAAAA70415,AAAAAA43678,AAAAAAAA72908}
(4 rows)
SELECT * FROM array_op_test WHERE t && '{AAAAAAAA72908}' ORDER BY seqno;
seqno | i | t
-------+-----------------------+--------------------------------------------------------------------------------------------------------------------------------------------
22 | {11,6,56,62,53,30} | {AAAAAAAA72908}
45 | {99,45} | {AAAAAAAA72908,AAAAAAAAAAAAAAAAAAA17075,AA88409,AAAAAAAAAAAAAAAAAA36842,AAAAAAA48038,AAAAAAAAAAAAAA10611}
72 | {22,1,16,78,20,91,83} | {47735,AAAAAAA56483,AAAAAAAAAAAAA93788,AA42406,AAAAAAAAAAAAA73084,AAAAAAAA72908,AAAAAAAAAAAAAAAAAA61286,AAAAA66674,AAAAAAAAAAAAAAAAA50407}
79 | {45} | {AAAAAAAAAA646,AAAAAAAAAAAAAAAAAAA70415,AAAAAA43678,AAAAAAAA72908}
(4 rows)
SELECT * FROM array_op_test WHERE t @> '{AAAAAAAAAA646}' ORDER BY seqno;
seqno | i | t
-------+------------------+--------------------------------------------------------------------
15 | {17,14,16,63,67} | {AA6416,AAAAAAAAAA646,AAAAA95309}
79 | {45} | {AAAAAAAAAA646,AAAAAAAAAAAAAAAAAAA70415,AAAAAA43678,AAAAAAAA72908}
96 | {23,97,43} | {AAAAAAAAAA646,A87088}
(3 rows)
SELECT * FROM array_op_test WHERE t && '{AAAAAAAAAA646}' ORDER BY seqno;
seqno | i | t
-------+------------------+--------------------------------------------------------------------
15 | {17,14,16,63,67} | {AA6416,AAAAAAAAAA646,AAAAA95309}
79 | {45} | {AAAAAAAAAA646,AAAAAAAAAAAAAAAAAAA70415,AAAAAA43678,AAAAAAAA72908}
96 | {23,97,43} | {AAAAAAAAAA646,A87088}
(3 rows)
SELECT * FROM array_op_test WHERE t @> '{AAAAAAAA72908,AAAAAAAAAA646}' ORDER BY seqno;
seqno | i | t
-------+------+--------------------------------------------------------------------
79 | {45} | {AAAAAAAAAA646,AAAAAAAAAAAAAAAAAAA70415,AAAAAA43678,AAAAAAAA72908}
(1 row)
SELECT * FROM array_op_test WHERE t && '{AAAAAAAA72908,AAAAAAAAAA646}' ORDER BY seqno;
seqno | i | t
-------+-----------------------+--------------------------------------------------------------------------------------------------------------------------------------------
15 | {17,14,16,63,67} | {AA6416,AAAAAAAAAA646,AAAAA95309}
22 | {11,6,56,62,53,30} | {AAAAAAAA72908}
45 | {99,45} | {AAAAAAAA72908,AAAAAAAAAAAAAAAAAAA17075,AA88409,AAAAAAAAAAAAAAAAAA36842,AAAAAAA48038,AAAAAAAAAAAAAA10611}
72 | {22,1,16,78,20,91,83} | {47735,AAAAAAA56483,AAAAAAAAAAAAA93788,AA42406,AAAAAAAAAAAAA73084,AAAAAAAA72908,AAAAAAAAAAAAAAAAAA61286,AAAAA66674,AAAAAAAAAAAAAAAAA50407}
79 | {45} | {AAAAAAAAAA646,AAAAAAAAAAAAAAAAAAA70415,AAAAAA43678,AAAAAAAA72908}
96 | {23,97,43} | {AAAAAAAAAA646,A87088}
(6 rows)
SELECT * FROM array_op_test WHERE t <@ '{AAAAAAAA72908,AAAAAAAAAAAAAAAAAAA17075,AA88409,AAAAAAAAAAAAAAAAAA36842,AAAAAAA48038,AAAAAAAAAAAAAA10611}' ORDER BY seqno;
seqno | i | t
-------+--------------------+-----------------------------------------------------------------------------------------------------------
22 | {11,6,56,62,53,30} | {AAAAAAAA72908}
45 | {99,45} | {AAAAAAAA72908,AAAAAAAAAAAAAAAAAAA17075,AA88409,AAAAAAAAAAAAAAAAAA36842,AAAAAAA48038,AAAAAAAAAAAAAA10611}
101 | {} | {}
(3 rows)
SELECT * FROM array_op_test WHERE t = '{}' ORDER BY seqno;
seqno | i | t
-------+----+----
101 | {} | {}
(1 row)
SELECT * FROM array_op_test WHERE t @> '{}' ORDER BY seqno;
seqno | i | t
-------+---------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 | {92,75,71,52,64,83} | {AAAAAAAA44066,AAAAAA1059,AAAAAAAAAAA176,AAAAAAA48038}
2 | {3,6} | {AAAAAA98232,AAAAAAAA79710,AAAAAAAAAAAAAAAAA69675,AAAAAAAAAAAAAAAA55798,AAAAAAAAA12793}
3 | {37,64,95,43,3,41,13,30,11,43} | {AAAAAAAAAA48845,AAAAA75968,AAAAA95309,AAA54451,AAAAAAAAAA22292,AAAAAAA99836,A96617,AA17009,AAAAAAAAAAAAAA95246}
4 | {71,39,99,55,33,75,45} | {AAAAAAAAA53663,AAAAAAAAAAAAAAA67062,AAAAAAAAAA64777,AAA99043,AAAAAAAAAAAAAAAAAAA91804,39557}
5 | {50,42,77,50,4} | {AAAAAAAAAAAAAAAAA26540,AAAAAAA79710,AAAAAAAAAAAAAAAAAAA1205,AAAAAAAAAAA176,AAAAA95309,AAAAAAAAAAA46154,AAAAAA66777,AAAAAAAAA27249,AAAAAAAAAA64777,AAAAAAAAAAAAAAAAAAA70104}
6 | {39,35,5,94,17,92,60,32} | {AAAAAAAAAAAAAAA35875,AAAAAAAAAAAAAAAA23657}
7 | {12,51,88,64,8} | {AAAAAAAAAAAAAAAAAA12591,AAAAAAAAAAAAAAAAA50407,AAAAAAAAAAAA67946}
8 | {60,84} | {AAAAAAA81898,AAAAAA1059,AAAAAAAAAAAA81511,AAAAA961,AAAAAAAAAAAAAAAA31334,AAAAA64741,AA6416,AAAAAAAAAAAAAAAAAA32918,AAAAAAAAAAAAAAAAA50407}
9 | {56,52,35,27,80,44,81,22} | {AAAAAAAAAAAAAAA73034,AAAAAAAAAAAAA7929,AAAAAAA66161,AA88409,39557,A27153,AAAAAAAA9523,AAAAAAAAAAA99000}
10 | {71,5,45} | {AAAAAAAAAAA21658,AAAAAAAAAAAA21089,AAA54451,AAAAAAAAAAAAAAAAAA54141,AAAAAAAAAAAAAA28620,AAAAAAAAAAA21658,AAAAAAAAAAA74076,AAAAAAAAA27249}
11 | {41,86,74,48,22,74,47,50} | {AAAAAAAA9523,AAAAAAAAAAAA37562,AAAAAAAAAAAAAAAA14047,AAAAAAAAAAA46154,AAAA41702,AAAAAAAAAAAAAAAAA764,AAAAA62737,39557}
12 | {17,99,18,52,91,72,0,43,96,23} | {AAAAA33250,AAAAAAAAAAAAAAAAAAA85420,AAAAAAAAAAA33576}
13 | {3,52,34,23} | {AAAAAA98232,AAAA49534,AAAAAAAAAAA21658}
14 | {78,57,19} | {AAAA8857,AAAAAAAAAAAAAAA73034,AAAAAAAA81587,AAAAAAAAAAAAAAA68526,AAAAA75968,AAAAAAAAAAAAAA65909,AAAAAAAAA10012,AAAAAAAAAAAAAA65909}
15 | {17,14,16,63,67} | {AA6416,AAAAAAAAAA646,AAAAA95309}
16 | {14,63,85,11} | {AAAAAA66777}
17 | {7,10,81,85} | {AAAAAA43678,AAAAAAA12144,AAAAAAAAAAA50956,AAAAAAAAAAAAAAAAAAA15356}
18 | {1} | {AAAAAAAAAAA33576,AAAAA95309,64261,AAA59323,AAAAAAAAAAAAAA95246,55847,AAAAAAAAAAAA67946,AAAAAAAAAAAAAAAAAA64374}
19 | {52,82,17,74,23,46,69,51,75} | {AAAAAAAAAAAAA73084,AAAAA75968,AAAAAAAAAAAAAAAA14047,AAAAAAA80240,AAAAAAAAAAAAAAAAAAA1205,A68938}
20 | {72,89,70,51,54,37,8,49,79} | {AAAAAA58494}
21 | {2,8,65,10,5,79,43} | {AAAAAAAAAAAAAAAAA88852,AAAAAAAAAAAAAAAAAAA91804,AAAAA64669,AAAAAAAAAAAAAAAA1443,AAAAAAAAAAAAAAAA23657,AAAAA12179,AAAAAAAAAAAAAAAAA88852,AAAAAAAAAAAAAAAA31334,AAAAAAAAAAAAAAAA41303,AAAAAAAAAAAAAAAAAAA85420}
22 | {11,6,56,62,53,30} | {AAAAAAAA72908}
23 | {40,90,5,38,72,40,30,10,43,55} | {A6053,AAAAAAAAAAA6119,AA44673,AAAAAAAAAAAAAAAAA764,AA17009,AAAAA17383,AAAAA70514,AAAAA33250,AAAAA95309,AAAAAAAAAAAA37562}
24 | {94,61,99,35,48} | {AAAAAAAAAAA50956,AAAAAAAAAAA15165,AAAA85070,AAAAAAAAAAAAAAA36627,AAAAA961,AAAAAAAAAA55219}
25 | {31,1,10,11,27,79,38} | {AAAAAAAAAAAAAAAAAA59334,45449}
26 | {71,10,9,69,75} | {47735,AAAAAAA21462,AAAAAAAAAAAAAAAAA6897,AAAAAAAAAAAAAAAAAAA91804,AAAAAAAAA72121,AAAAAAAAAAAAAAAAAAA1205,AAAAA41597,AAAA8857,AAAAAAAAAAAAAAAAAAA15356,AA17009}
27 | {94} | {AA6416,A6053,AAAAAAA21462,AAAAAAA57334,AAAAAAAAAAAAAAAAAA12591,AA88409,AAAAAAAAAAAAA70254}
28 | {14,33,6,34,14} | {AAAAAAAAAAAAAAA13198,AAAAAAAA69452,AAAAAAAAAAA82945,AAAAAAA12144,AAAAAAAAA72121,AAAAAAAAAA18601}
29 | {39,21} | {AAAAAAAAAAAAAAAAA6897,AAAAAAAAAAAAAAAAAAA38885,AAAA85070,AAAAAAAAAAAAAAAAAAA70104,AAAAA66674,AAAAAAAAAAAAA62007,AAAAAAAA69452,AAAAAAA1242,AAAAAAAAAAAAAAAA1729,AAAA35194}
30 | {26,81,47,91,34} | {AAAAAAAAAAAAAAAAAAA70104,AAAAAAA80240}
31 | {80,24,18,21,54} | {AAAAAAAAAAAAAAA13198,AAAAAAAAAAAAAAAAAAA70415,A27153,AAAAAAAAA53663,AAAAAAAAAAAAAAAAA50407,A68938}
32 | {58,79,82,80,67,75,98,10,41} | {AAAAAAAAAAAAAAAAAA61286,AAA54451,AAAAAAAAAAAAAAAAAAA87527,A96617,51533}
33 | {74,73} | {A85417,AAAAAAA56483,AAAAA17383,AAAAAAAAAAAAA62159,AAAAAAAAAAAA52814,AAAAAAAAAAAAA85723,AAAAAAAAAAAAAAAAAA55796}
34 | {70,45} | {AAAAAAAAAAAAAAAAAA71621,AAAAAAAAAAAAAA28620,AAAAAAAAAA55219,AAAAAAAA23648,AAAAAAAAAA22292,AAAAAAA1242}
35 | {23,40} | {AAAAAAAAAAAA52814,AAAA48949,AAAAAAAAA34727,AAAA8857,AAAAAAAAAAAAAAAAAAA62179,AAAAAAAAAAAAAAA68526,AAAAAAA99836,AAAAAAAA50094,AAAA91194,AAAAAAAAAAAAA73084}
36 | {79,82,14,52,30,5,79} | {AAAAAAAAA53663,AAAAAAAAAAAAAAAA55798,AAAAAAAAAAAAAAAAAAA89194,AA88409,AAAAAAAAAAAAAAA81326,AAAAAAAAAAAAAAAAA63050,AAAAAAAAAAAAAAAA33598}
37 | {53,11,81,39,3,78,58,64,74} | {AAAAAAAAAAAAAAAAAAA17075,AAAAAAA66161,AAAAAAAA23648,AAAAAAAAAAAAAA10611}
38 | {59,5,4,95,28} | {AAAAAAAAAAA82945,A96617,47735,AAAAA12179,AAAAA64669,AAAAAA99807,AA74433,AAAAAAAAAAAAAAAAA59387}
39 | {82,43,99,16,74} | {AAAAAAAAAAAAAAA67062,AAAAAAA57334,AAAAAAAAAAAAAA65909,A27153,AAAAAAAAAAAAAAAAAAA17075,AAAAAAAAAAAAAAAAA43052,AAAAAAAAAA64777,AAAAAAAAAAAA81511,AAAAAAAAAAAAAA65909,AAAAAAAAAAAAAA28620}
40 | {34} | {AAAAAAAAAAAAAA10611,AAAAAAAAAAAAAAAAAAA1205,AAAAAAAAAAA50956,AAAAAAAAAAAAAAAA31334,AAAAA70466,AAAAAAAA81587,AAAAAAA74623}
41 | {19,26,63,12,93,73,27,94} | {AAAAAAA79710,AAAAAAAAAA55219,AAAA41702,AAAAAAAAAAAAAAAAAAA17075,AAAAAAAAAAAAAAAAAA71621,AAAAAAAAAAAAAAAAA63050,AAAAAAA99836,AAAAAAAAAAAAAA8666}
42 | {15,76,82,75,8,91} | {AAAAAAAAAAA176,AAAAAA38063,45449,AAAAAA54032,AAAAAAA81898,AA6416,AAAAAAAAAAAAAAAAAAA62179,45449,AAAAA60038,AAAAAAAA81587}
43 | {39,87,91,97,79,28} | {AAAAAAAAAAA74076,A96617,AAAAAAAAAAAAAAAAAAA89194,AAAAAAAAAAAAAAAAAA55796,AAAAAAAAAAAAAAAA23657,AAAAAAAAAAAA67946}
44 | {40,58,68,29,54} | {AAAAAAA81898,AAAAAA66777,AAAAAA98232}
45 | {99,45} | {AAAAAAAA72908,AAAAAAAAAAAAAAAAAAA17075,AA88409,AAAAAAAAAAAAAAAAAA36842,AAAAAAA48038,AAAAAAAAAAAAAA10611}
46 | {53,24} | {AAAAAAAAAAA53908,AAAAAA54032,AAAAA17383,AAAA48949,AAAAAAAAAA18601,AAAAA64669,45449,AAAAAAAAAAA98051,AAAAAAAAAAAAAAAAAA71621}
47 | {98,23,64,12,75,61} | {AAA59323,AAAAA95309,AAAAAAAAAAAAAAAA31334,AAAAAAAAA27249,AAAAA17383,AAAAAAAAAAAA37562,AAAAAA1059,A84822,55847,AAAAA70466}
48 | {76,14} | {AAAAAAAAAAAAA59671,AAAAAAAAAAAAAAAAAAA91804,AAAAAA66777,AAAAAAAAAAAAAAAAAAA89194,AAAAAAAAAAAAAAA36627,AAAAAAAAAAAAAAAAAAA17075,AAAAAAAAAAAAA73084,AAAAAAA79710,AAAAAAAAAAAAAAA40402,AAAAAAAAAAAAAAAAAAA65037}
49 | {56,5,54,37,49} | {AA21643,AAAAAAAAAAA92631,AAAAAAAA81587}
50 | {20,12,37,64,93} | {AAAAAAAAAA5483,AAAAAAAAAAAAAAAAAAA1205,AA6416,AAAAAAAAAAAAAAAAA63050,AAAAAAAAAAAAAAAAAA47955}
51 | {47} | {AAAAAAAAAAAAAA96505,AAAAAAAAAAAAAAAAAA36842,AAAAA95309,AAAAAAAA81587,AA6416,AAAA91194,AAAAAA58494,AAAAAA1059,AAAAAAAA69452}
52 | {89,0} | {AAAAAAAAAAAAAAAAAA47955,AAAAAAA48038,AAAAAAAAAAAAAAAAA43052,AAAAAAAAAAAAA73084,AAAAA70466,AAAAAAAAAAAAAAAAA764,AAAAAAAAAAA46154,AA66862}
53 | {38,17} | {AAAAAAAAAAA21658}
54 | {70,47} | {AAAAAAAAAAAAAAAAAA54141,AAAAA40681,AAAAAAA48038,AAAAAAAAAAAAAAAA29150,AAAAA41597,AAAAAAAAAAAAAAAAAA59334,AA15322}
55 | {47,79,47,64,72,25,71,24,93} | {AAAAAAAAAAAAAAAAAA55796,AAAAA62737}
56 | {33,7,60,54,93,90,77,85,39} | {AAAAAAAAAAAAAAAAAA32918,AA42406}
57 | {23,45,10,42,36,21,9,96} | {AAAAAAAAAAAAAAAAAAA70415}
58 | {92} | {AAAAAAAAAAAAAAAA98414,AAAAAAAA23648,AAAAAAAAAAAAAAAAAA55796,AA25381,AAAAAAAAAAA6119}
59 | {9,69,46,77} | {39557,AAAAAAA89932,AAAAAAAAAAAAAAAAA43052,AAAAAAAAAAAAAAAAA26540,AAA20874,AA6416,AAAAAAAAAAAAAAAAAA47955}
60 | {62,2,59,38,89} | {AAAAAAA89932,AAAAAAAAAAAAAAAAAAA15356,AA99927,AA17009,AAAAAAAAAAAAAAA35875}
61 | {72,2,44,95,54,54,13} | {AAAAAAAAAAAAAAAAAAA91804}
62 | {83,72,29,73} | {AAAAAAAAAAAAA15097,AAAA8857,AAAAAAAAAAAA35809,AAAAAAAAAAAA52814,AAAAAAAAAAAAAAAAAAA38885,AAAAAAAAAAAAAAAAAA24183,AAAAAA43678,A96617}
63 | {11,4,61,87} | {AAAAAAAAA27249,AAAAAAAAAAAAAAAAAA32918,AAAAAAAAAAAAAAA13198,AAA20874,39557,51533,AAAAAAAAAAA53908,AAAAAAAAAAAAAA96505,AAAAAAAA78938}
64 | {26,19,34,24,81,78} | {A96617,AAAAAAAAAAAAAAAAAAA70104,A68938,AAAAAAAAAAA53908,AAAAAAAAAAAAAAA453,AA17009,AAAAAAA80240}
65 | {61,5,76,59,17} | {AAAAAA99807,AAAAA64741,AAAAAAAAAAA53908,AA21643,AAAAAAAAA10012}
66 | {31,23,70,52,4,33,48,25} | {AAAAAAAAAAAAAAAAA69675,AAAAAAAA50094,AAAAAAAAAAA92631,AAAA35194,39557,AAAAAAA99836}
67 | {31,94,7,10} | {AAAAAA38063,A96617,AAAA35194,AAAAAAAAAAAA67946}
68 | {90,43,38} | {AA75092,AAAAAAAAAAAAAAAAA69675,AAAAAAAAAAA92631,AAAAAAAAA10012,AAAAAAAAAAAAA7929,AA21643}
69 | {67,35,99,85,72,86,44} | {AAAAAAAAAAAAAAAAAAA1205,AAAAAAAA50094,AAAAAAAAAAAAAAAA1729,AAAAAAAAAAAAAAAAAA47955}
70 | {56,70,83} | {AAAA41702,AAAAAAAAAAA82945,AA21643,AAAAAAAAAAA99000,A27153,AA25381,AAAAAAAAAAAAAA96505,AAAAAAA1242}
71 | {74,26} | {AAAAAAAAAAA50956,AA74433,AAAAAAA21462,AAAAAAAAAAAAAAAAAAA17075,AAAAAAAAAAAAAAA36627,AAAAAAAAAAAAA70254,AAAAAAAAAA43419,39557}
72 | {22,1,16,78,20,91,83} | {47735,AAAAAAA56483,AAAAAAAAAAAAA93788,AA42406,AAAAAAAAAAAAA73084,AAAAAAAA72908,AAAAAAAAAAAAAAAAAA61286,AAAAA66674,AAAAAAAAAAAAAAAAA50407}
73 | {88,25,96,78,65,15,29,19} | {AAA54451,AAAAAAAAA27249,AAAAAAA9228,AAAAAAAAAAAAAAA67062,AAAAAAAAAAAAAAAAAAA70415,AAAAA17383,AAAAAAAAAAAAAAAA33598}
74 | {32} | {AAAAAAAAAAAAAAAA1729,AAAAAAAAAAAAA22860,AAAAAA99807,AAAAA17383,AAAAAAAAAAAAAAA67062,AAAAAAAAAAA15165,AAAAAAAAAAA50956}
75 | {12,96,83,24,71,89,55} | {AAAA48949,AAAAAAAA29716,AAAAAAAAAAAAAAAAAAA1205,AAAAAAAAAAAA67946,AAAAAAAAAAAAAAAA29150,AAA28075,AAAAAAAAAAAAAAAAA43052}
76 | {92,55,10,7} | {AAAAAAAAAAAAAAA67062}
77 | {97,15,32,17,55,59,18,37,50,39} | {AAAAAAAAAAAA67946,AAAAAA54032,AAAAAAAA81587,55847,AAAAAAAAAAAAAA28620,AAAAAAAAAAAAAAAAA43052,AAAAAA75463,AAAA49534,AAAAAAAA44066}
78 | {55,89,44,84,34} | {AAAAAAAAAAA6119,AAAAAAAAAAAAAA8666,AA99927,AA42406,AAAAAAA81898,AAAAAAA9228,AAAAAAAAAAA92631,AA21643,AAAAAAAAAAAAAA28620}
79 | {45} | {AAAAAAAAAA646,AAAAAAAAAAAAAAAAAAA70415,AAAAAA43678,AAAAAAAA72908}
80 | {74,89,44,80,0} | {AAAA35194,AAAAAAAA79710,AAA20874,AAAAAAAAAAAAAAAAAAA70104,AAAAAAAAAAAAA73084,AAAAAAA57334,AAAAAAA9228,AAAAAAAAAAAAA62007}
81 | {63,77,54,48,61,53,97} | {AAAAAAAAAAAAAAA81326,AAAAAAAAAA22292,AA25381,AAAAAAAAAAA74076,AAAAAAA81898,AAAAAAAAA72121}
82 | {34,60,4,79,78,16,86,89,42,50} | {AAAAA40681,AAAAAAAAAAAAAAAAAA12591,AAAAAAA80240,AAAAAAAAAAAAAAAA55798,AAAAAAAAAAAAAAAAAAA70104}
83 | {14,10} | {AAAAAAAAAA22292,AAAAAAAAAAAAA70254,AAAAAAAAAAA6119}
84 | {11,83,35,13,96,94} | {AAAAA95309,AAAAAAAAAAAAAAAAAA32918,AAAAAAAAAAAAAAAAAA24183}
85 | {39,60} | {AAAAAAAAAAAAAAAA55798,AAAAAAAAAA22292,AAAAAAA66161,AAAAAAA21462,AAAAAAAAAAAAAAAAAA12591,55847,AAAAAA98232,AAAAAAAAAAA46154}
86 | {33,81,72,74,45,36,82} | {AAAAAAAA81587,AAAAAAAAAAAAAA96505,45449,AAAA80176}
87 | {57,27,50,12,97,68} | {AAAAAAAAAAAAAAAAA26540,AAAAAAAAA10012,AAAAAAAAAAAA35809,AAAAAAAAAAAAAAAA29150,AAAAAAAAAAA82945,AAAAAA66777,31228,AAAAAAAAAAAAAAAA23657,AAAAAAAAAAAAAA28620,AAAAAAAAAAAAAA96505}
88 | {41,90,77,24,6,24} | {AAAA35194,AAAA35194,AAAAAAA80240,AAAAAAAAAAA46154,AAAAAA58494,AAAAAAAAAAAAAAAAAAA17075,AAAAAAAAAAAAAAAAAA59334,AAAAAAAAAAAAAAAAAAA91804,AA74433}
89 | {40,32,17,6,30,88} | {AA44673,AAAAAAAAAAA6119,AAAAAAAAAAAAAAAA23657,AAAAAAAAAAAAAAAAAA47955,AAAAAAAAAAAAAAAA33598,AAAAAAAAAAA33576,AA44673}
90 | {88,75} | {AAAAA60038,AAAAAAAA23648,AAAAAAAAAAA99000,AAAA41702,AAAAAAAAAAAAA22860,AAAAAAAAAAAAAAA68526}
91 | {78} | {AAAAAAAAAAAAA62007,AAA99043}
92 | {85,63,49,45} | {AAAAAAA89932,AAAAAAAAAAAAA22860,AAAAAAAAAAAAAAAAAAA1205,AAAAAAAAAAAA21089}
93 | {11} | {AAAAAAAAAAA176,AAAAAAAAAAAAAA8666,AAAAAAAAAAAAAAA453,AAAAAAAAAAAAA85723,A68938,AAAAAAAAAAAAA9821,AAAAAAA48038,AAAAAAAAAAAAAAAAA59387,AA99927,AAAAA17383}
94 | {98,9,85,62,88,91,60,61,38,86} | {AAAAAAAA81587,AAAAA17383,AAAAAAAA81587}
95 | {47,77} | {AAAAAAAAAAAAAAAAA764,AAAAAAAAAAA74076,AAAAAAAAAA18107,AAAAA40681,AAAAAAAAAAAAAAA35875,AAAAA60038,AAAAAAA56483}
96 | {23,97,43} | {AAAAAAAAAA646,A87088}
97 | {54,2,86,65} | {47735,AAAAAAA99836,AAAAAAAAAAAAAAAAA6897,AAAAAAAAAAAAAAAA29150,AAAAAAA80240,AAAAAAAAAAAAAAAA98414,AAAAAAA56483,AAAAAAAAAAAAAAAA29150,AAAAAAA39692,AA21643}
98 | {38,34,32,89} | {AAAAAAAAAAAAAAAAAA71621,AAAA8857,AAAAAAAAAAAAAAAAAAA65037,AAAAAAAAAAAAAAAA31334,AAAAAAAAAA48845}
99 | {37,86} | {AAAAAAAAAAAAAAAAAA32918,AAAAA70514,AAAAAAAAA10012,AAAAAAAAAAAAAAAAA59387,AAAAAAAAAA64777,AAAAAAAAAAAAAAAAAAA15356}
100 | {85,32,57,39,49,84,32,3,30} | {AAAAAAA80240,AAAAAAAAAAAAAAAA1729,AAAAA60038,AAAAAAAAAAA92631,AAAAAAAA9523}
101 | {} | {}
102 | {NULL} | {NULL}
(102 rows)
SELECT * FROM array_op_test WHERE t && '{}' ORDER BY seqno;
seqno | i | t
-------+---+---
(0 rows)
SELECT * FROM array_op_test WHERE t <@ '{}' ORDER BY seqno;
seqno | i | t
-------+----+----
101 | {} | {}
(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 pg_typeof(ARRAY[1,2,3]::text[]::int[]::float8[]) AS "double precision[]";
double precision[]
--------------------
double precision[]
(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 pg_typeof(ARRAY[['a','bc'],['def','hijk']]::text[]::varchar[]) AS "character varying[]";
character varying[]
---------------------
character varying[]
(1 row)
SELECT CAST(ARRAY[[[[[['a','bb','ccc']]]]]] as text[]) as "{{{{{{a,bb,ccc}}}}}}";
{{{{{{a,bb,ccc}}}}}}
----------------------
{{{{{{a,bb,ccc}}}}}}
(1 row)
SELECT NULL::text[]::int[] AS "NULL";
NULL
------
(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
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)
-- nulls later in the bitmap
SELECT -1 != ALL(ARRAY(SELECT NULLIF(g.i, 900) FROM generate_series(1,1000) g(i)));
?column?
----------
(1 row)
-- test indexes on arrays
create temp table arr_tbl (f1 int[] unique) DISTRIBUTED BY (f1);
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"
DETAIL: Key (f1)=({1,2,3}) already exists.
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}' ORDER BY 1;
f1
----------
{1,2,10}
{1,5,3}
(2 rows)
select * from arr_tbl where f1 >= '{1,2,3}' and f1 < '{1,5,3}' ORDER BY 1;
f1
----------
{1,2,3}
{1,2,10}
(2 rows)
-- test ON CONFLICT DO UPDATE with arrays
create temp table arr_pk_tbl (pk int4 primary key, f1 int[]);
insert into arr_pk_tbl values (1, '{1,2,3}');
insert into arr_pk_tbl values (1, '{3,4,5}') on conflict (pk)
do update set f1[1] = excluded.f1[1], f1[3] = excluded.f1[3]
returning pk, f1;
pk | f1
----+---------
1 | {3,2,5}
(1 row)
insert into arr_pk_tbl(pk, f1[1:2]) values (1, '{6,7,8}') on conflict (pk)
do update set f1[1] = excluded.f1[1],
f1[2] = excluded.f1[2],
f1[3] = excluded.f1[3]
returning pk, f1;
pk | f1
----+------------
1 | {6,7,NULL}
(1 row)
-- note: if above selects don'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 subscript overflow detection
-- The normal error message includes a platform-dependent limit,
-- so suppress it to avoid needing multiple expected-files.
\set VERBOSITY sqlstate
insert into arr_pk_tbl values(10, '[-2147483648:-2147483647]={1,2}');
update arr_pk_tbl set f1[2147483647] = 42 where pk = 10;
ERROR: 54000
update arr_pk_tbl set f1[2147483646:2147483647] = array[4,2] where pk = 10;
ERROR: 54000
insert into arr_pk_tbl(pk, f1[0:2147483647]) values (2, '{}');
ERROR: 54000
insert into arr_pk_tbl(pk, f1[-2147483648:2147483647]) values (2, '{}');
ERROR: 54000
-- also exercise the expanded-array case
do $$ declare a int[];
begin
a := '[-2147483648:-2147483647]={1,2}'::int[];
a[2147483647] := 42;
end $$;
ERROR: 54000
\set VERBOSITY default
-- 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}}"
LINE 1: select '{{1,{2}},{2,3}}'::text[];
^
DETAIL: Unexpected "{" character.
select '{{},{}}'::text[];
ERROR: malformed array literal: "{{},{}}"
LINE 1: select '{{},{}}'::text[];
^
DETAIL: Unexpected "}" character.
select E'{{1,2},\\{2,3}}'::text[];
ERROR: malformed array literal: "{{1,2},\{2,3}}"
LINE 1: select E'{{1,2},\\{2,3}}'::text[];
^
DETAIL: Unexpected "\" character.
select '{{"1 2" x},{3}}'::text[];
ERROR: malformed array literal: "{{"1 2" x},{3}}"
LINE 1: select '{{"1 2" x},{3}}'::text[];
^
DETAIL: Unexpected array element.
select '{}}'::text[];
ERROR: malformed array literal: "{}}"
LINE 1: select '{}}'::text[];
^
DETAIL: Junk after closing right brace.
select '{ }}'::text[];
ERROR: malformed array literal: "{ }}"
LINE 1: select '{ }}'::text[];
^
DETAIL: Junk after closing right brace.
select array[];
ERROR: cannot determine type of empty array
LINE 1: select array[];
^
HINT: Explicitly cast to the desired type, for example ARRAY[]::integer[].
-- 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)
select array[]::text[];
array
-------
{}
(1 row)
select '[0:1]={1.1,2.2}'::float8[];
float8
-----------------
[0:1]={1.1,2.2}
(1 row)
-- all of the above should be accepted
-- tests for array aggregates
CREATE TEMP TABLE arraggtest ( f1 INT[], f2 TEXT[][], f3 FLOAT[]) DISTRIBUTED RANDOMLY;
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[], distkey int4) distributed by (distkey);
-- 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 c1, c2 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;
create or replace function unnest1(anyarray)
returns setof anyelement as $$
select $1[s] from generate_subscripts($1,1) g(s);
$$ language sql immutable;
create or replace function unnest2(anyarray)
returns setof anyelement as $$
select $1[s1][s2] from generate_subscripts($1,1) g1(s1),
generate_subscripts($1,2) g2(s2);
$$ language sql immutable;
select * from unnest1(array[1,2,3]);
unnest1
---------
1
2
3
(3 rows)
select * from unnest2(array[[1,2,3],[4,5,6]]);
unnest2
---------
1
2
3
4
5
6
(6 rows)
drop function unnest1(anyarray);
drop function unnest2(anyarray);
select array_fill(null::integer, array[3,3],array[2,2]);
array_fill
-----------------------------------------------------------------
[2:4][2:4]={{NULL,NULL,NULL},{NULL,NULL,NULL},{NULL,NULL,NULL}}
(1 row)
select array_fill(null::integer, array[3,3]);
array_fill
------------------------------------------------------
{{NULL,NULL,NULL},{NULL,NULL,NULL},{NULL,NULL,NULL}}
(1 row)
select array_fill(null::text, array[3,3],array[2,2]);
array_fill
-----------------------------------------------------------------
[2:4][2:4]={{NULL,NULL,NULL},{NULL,NULL,NULL},{NULL,NULL,NULL}}
(1 row)
select array_fill(null::text, array[3,3]);
array_fill
------------------------------------------------------
{{NULL,NULL,NULL},{NULL,NULL,NULL},{NULL,NULL,NULL}}
(1 row)
select array_fill(7, array[3,3],array[2,2]);
array_fill
--------------------------------------
[2:4][2:4]={{7,7,7},{7,7,7},{7,7,7}}
(1 row)
select array_fill(7, array[3,3]);
array_fill
---------------------------
{{7,7,7},{7,7,7},{7,7,7}}
(1 row)
select array_fill('juhu'::text, array[3,3],array[2,2]);
array_fill
-----------------------------------------------------------------
[2:4][2:4]={{juhu,juhu,juhu},{juhu,juhu,juhu},{juhu,juhu,juhu}}
(1 row)
select array_fill('juhu'::text, array[3,3]);
array_fill
------------------------------------------------------
{{juhu,juhu,juhu},{juhu,juhu,juhu},{juhu,juhu,juhu}}
(1 row)
select a, a = '{}' as is_eq, array_dims(a)
from (select array_fill(42, array[0]) as a) ss;
a | is_eq | array_dims
----+-------+------------
{} | t |
(1 row)
select a, a = '{}' as is_eq, array_dims(a)
from (select array_fill(42, '{}') as a) ss;
a | is_eq | array_dims
----+-------+------------
{} | t |
(1 row)
select a, a = '{}' as is_eq, array_dims(a)
from (select array_fill(42, '{}', '{}') as a) ss;
a | is_eq | array_dims
----+-------+------------
{} | t |
(1 row)
-- raise exception
select array_fill(1, null, array[2,2]);
ERROR: dimension array or low bound array cannot be null
select array_fill(1, array[2,2], null);
ERROR: dimension array or low bound array cannot be null
select array_fill(1, array[2,2], '{}');
ERROR: wrong number of array subscripts
DETAIL: Low bound array has different size than dimensions array.
select array_fill(1, array[3,3], array[1,1,1]);
ERROR: wrong number of array subscripts
DETAIL: Low bound array has different size than dimensions array.
select array_fill(1, array[1,2,null]);
ERROR: dimension values cannot be null
select array_fill(1, array[[1,2],[3,4]]);
ERROR: wrong number of array subscripts
DETAIL: Dimension array must be one dimensional.
select string_to_array('1|2|3', '|');
string_to_array
-----------------
{1,2,3}
(1 row)
select string_to_array('1|2|3|', '|');
string_to_array
-----------------
{1,2,3,""}
(1 row)
select string_to_array('1||2|3||', '||');
string_to_array
-----------------
{1,2|3,""}
(1 row)
select string_to_array('1|2|3', '');
string_to_array
-----------------
{1|2|3}
(1 row)
select string_to_array('', '|');
string_to_array
-----------------
{}
(1 row)
select string_to_array('1|2|3', NULL);
string_to_array
-----------------
{1,|,2,|,3}
(1 row)
select string_to_array(NULL, '|') IS NULL;
?column?
----------
t
(1 row)
select string_to_array('abc', '');
string_to_array
-----------------
{abc}
(1 row)
select string_to_array('abc', '', 'abc');
string_to_array
-----------------
{NULL}
(1 row)
select string_to_array('abc', ',');
string_to_array
-----------------
{abc}
(1 row)
select string_to_array('abc', ',', 'abc');
string_to_array
-----------------
{NULL}
(1 row)
select string_to_array('1,2,3,4,,6', ',');
string_to_array
-----------------
{1,2,3,4,"",6}
(1 row)
select string_to_array('1,2,3,4,,6', ',', '');
string_to_array
------------------
{1,2,3,4,NULL,6}
(1 row)
select string_to_array('1,2,3,4,*,6', ',', '*');
string_to_array
------------------
{1,2,3,4,NULL,6}
(1 row)
select v, v is null as "is null" from string_to_table('1|2|3', '|') g(v);
v | is null
---+---------
1 | f
2 | f
3 | f
(3 rows)
select v, v is null as "is null" from string_to_table('1|2|3|', '|') g(v);
v | is null
---+---------
1 | f
2 | f
3 | f
| f
(4 rows)
select v, v is null as "is null" from string_to_table('1||2|3||', '||') g(v);
v | is null
-----+---------
1 | f
2|3 | f
| f
(3 rows)
select v, v is null as "is null" from string_to_table('1|2|3', '') g(v);
v | is null
-------+---------
1|2|3 | f
(1 row)
select v, v is null as "is null" from string_to_table('', '|') g(v);
v | is null
---+---------
(0 rows)
select v, v is null as "is null" from string_to_table('1|2|3', NULL) g(v);
v | is null
---+---------
1 | f
| | f
2 | f
| | f
3 | f
(5 rows)
select v, v is null as "is null" from string_to_table(NULL, '|') g(v);
v | is null
---+---------
(0 rows)
select v, v is null as "is null" from string_to_table('abc', '') g(v);
v | is null
-----+---------
abc | f
(1 row)
select v, v is null as "is null" from string_to_table('abc', '', 'abc') g(v);
v | is null
---+---------
| t
(1 row)
select v, v is null as "is null" from string_to_table('abc', ',') g(v);
v | is null
-----+---------
abc | f
(1 row)
select v, v is null as "is null" from string_to_table('abc', ',', 'abc') g(v);
v | is null
---+---------
| t
(1 row)
select v, v is null as "is null" from string_to_table('1,2,3,4,,6', ',') g(v);
v | is null
---+---------
1 | f
2 | f
3 | f
4 | f
| f
6 | f
(6 rows)
select v, v is null as "is null" from string_to_table('1,2,3,4,,6', ',', '') g(v);
v | is null
---+---------
1 | f
2 | f
3 | f
4 | f
| t
6 | f
(6 rows)
select v, v is null as "is null" from string_to_table('1,2,3,4,*,6', ',', '*') g(v);
v | is null
---+---------
1 | f
2 | f
3 | f
4 | f
| t
6 | f
(6 rows)
select array_to_string(NULL::int4[], ',') IS NULL;
?column?
----------
t
(1 row)
select array_to_string('{}'::int4[], ',');
array_to_string
-----------------
(1 row)
select array_to_string(array[1,2,3,4,NULL,6], ',');
array_to_string
-----------------
1,2,3,4,6
(1 row)
select array_to_string(array[1,2,3,4,NULL,6], ',', '*');
array_to_string
-----------------
1,2,3,4,*,6
(1 row)
select array_to_string(array[1,2,3,4,NULL,6], NULL);
array_to_string
-----------------
(1 row)
select array_to_string(array[1,2,3,4,NULL,6], ',', NULL);
array_to_string
-----------------
1,2,3,4,6
(1 row)
select array_to_string(string_to_array('1|2|3', '|'), '|');
array_to_string
-----------------
1|2|3
(1 row)
select array_length(array[1,2,3], 1);
array_length
--------------
3
(1 row)
select array_length(array[[1,2,3], [4,5,6]], 0);
array_length
--------------
(1 row)
select array_length(array[[1,2,3], [4,5,6]], 1);
array_length
--------------
2
(1 row)
select array_length(array[[1,2,3], [4,5,6]], 2);
array_length
--------------
3
(1 row)
select array_length(array[[1,2,3], [4,5,6]], 3);
array_length
--------------
(1 row)
select cardinality(NULL::int[]);
cardinality
-------------
(1 row)
select cardinality('{}'::int[]);
cardinality
-------------
0
(1 row)
select cardinality(array[1,2,3]);
cardinality
-------------
3
(1 row)
select cardinality('[2:4]={5,6,7}'::int[]);
cardinality
-------------
3
(1 row)
select cardinality('{{1,2}}'::int[]);
cardinality
-------------
2
(1 row)
select cardinality('{{1,2},{3,4},{5,6}}'::int[]);
cardinality
-------------
6
(1 row)
select cardinality('{{{1,9},{5,6}},{{2,3},{3,4}}}'::int[]);
cardinality
-------------
8
(1 row)
select array_agg(unique1 order by unique1) from (select unique1 from tenk1 where unique1 < 15 order by unique1) ss;
array_agg
--------------------------------------
{0,1,2,3,4,5,6,7,8,9,10,11,12,13,14}
(1 row)
select array_agg(ten order by ten) from (select ten from tenk1 where unique1 < 15 order by unique1) ss;
array_agg
---------------------------------
{0,0,1,1,2,2,3,3,4,4,5,6,7,8,9}
(1 row)
select array_agg(nullif(ten, 4) order by ten) from (select ten from tenk1 where unique1 < 15 order by unique1) ss;
array_agg
---------------------------------------
{0,0,1,1,2,2,3,3,NULL,NULL,5,6,7,8,9}
(1 row)
select array_agg(unique1 order by unique1) from tenk1 where unique1 < -15;
array_agg
-----------
(1 row)
-- array_agg(anyarray)
select array_agg(ar)
from (values ('{1,2}'::int[]), ('{3,4}'::int[])) v(ar);
array_agg
---------------
{{1,2},{3,4}}
(1 row)
select array_agg(distinct ar order by ar desc)
from (select array[i / 2] from generate_series(1,10) a(i)) b(ar);
array_agg
---------------------------
{{5},{4},{3},{2},{1},{0}}
(1 row)
select array_agg(ar)
from (select array_agg(array[i, i+1, i-1])
from generate_series(1,2) a(i)) b(ar);
array_agg
---------------------
{{{1,2,0},{2,3,1}}}
(1 row)
select array_agg(array[i+1.2, i+1.3, i+1.4]) from generate_series(1,3) g(i);
array_agg
---------------------------------------------
{{2.2,2.3,2.4},{3.2,3.3,3.4},{4.2,4.3,4.4}}
(1 row)
select array_agg(array['Hello', i::text]) from generate_series(9,11) g(i);
array_agg
-----------------------------------
{{Hello,9},{Hello,10},{Hello,11}}
(1 row)
select array_agg(array[i, nullif(i, 3), i+1]) from generate_series(1,4) g(i);
array_agg
--------------------------------------
{{1,1,2},{2,2,3},{3,NULL,4},{4,4,5}}
(1 row)
-- errors
select array_agg('{}'::int[]) from generate_series(1,2);
ERROR: data type anyarray is not an array type
select array_agg(null::int[]) from generate_series(1,2);
ERROR: data type anyarray is not an array type
select array_agg(ar)
from (values ('{1,2}'::int[]), ('{3}'::int[])) v(ar);
ERROR: cannot accumulate arrays of different dimensionality
select unnest(array[1,2,3]);
unnest
--------
1
2
3
(3 rows)
select * from unnest(array[1,2,3]);
unnest
--------
1
2
3
(3 rows)
select unnest(array[1,2,3,4.5]::float8[]);
unnest
--------
1
2
3
4.5
(4 rows)
select unnest(array[1,2,3,4.5]::numeric[]);
unnest
--------
1
2
3
4.5
(4 rows)
select unnest(array[1,2,3,null,4,null,null,5,6]);
unnest
--------
1
2
3
4
5
6
(9 rows)
select unnest(array[1,2,3,null,4,null,null,5,6]::text[]);
unnest
--------
1
2
3
4
5
6
(9 rows)
select abs(unnest(array[1,2,null,-3]));
abs
-----
1
2
3
(4 rows)
select array_remove(array[1,2,2,3], 2);
array_remove
--------------
{1,3}
(1 row)
select array_remove(array[1,2,2,3], 5);
array_remove
--------------
{1,2,2,3}
(1 row)
select array_remove(array[1,NULL,NULL,3], NULL);
array_remove
--------------
{1,3}
(1 row)
select array_remove(array['A','CC','D','C','RR'], 'RR');
array_remove
--------------
{A,CC,D,C}
(1 row)
select array_remove(array[1.0, 2.1, 3.3], 1);
array_remove
--------------
{2.1,3.3}
(1 row)
select array_remove('{{1,2,2},{1,4,3}}', 2); -- not allowed
ERROR: removing elements from multidimensional arrays is not supported
select array_remove(array['X','X','X'], 'X') = '{}';
?column?
----------
t
(1 row)
select array_replace(array[1,2,5,4],5,3);
array_replace
---------------
{1,2,3,4}
(1 row)
select array_replace(array[1,2,5,4],5,NULL);
array_replace
---------------
{1,2,NULL,4}
(1 row)
select array_replace(array[1,2,NULL,4,NULL],NULL,5);
array_replace
---------------
{1,2,5,4,5}
(1 row)
select array_replace(array['A','B','DD','B'],'B','CC');
array_replace
---------------
{A,CC,DD,CC}
(1 row)
select array_replace(array[1,NULL,3],NULL,NULL);
array_replace
---------------
{1,NULL,3}
(1 row)
select array_replace(array['AB',NULL,'CDE'],NULL,'12');
array_replace
---------------
{AB,12,CDE}
(1 row)
-- array(select array-value ...)
select array(select array[i,i/2] from generate_series(1,5) i);
array
---------------------------------
{{1,0},{2,1},{3,1},{4,2},{5,2}}
(1 row)
select array(select array['Hello', i::text] from generate_series(9,11) i);
array
-----------------------------------
{{Hello,9},{Hello,10},{Hello,11}}
(1 row)
-- int2vector and oidvector should be treated as scalar types for this purpose
select pg_typeof(array(select '11 22 33'::int2vector from generate_series(1,5)));
pg_typeof
--------------
int2vector[]
(1 row)
select array(select '11 22 33'::int2vector from generate_series(1,5));
array
----------------------------------------------------------
{"11 22 33","11 22 33","11 22 33","11 22 33","11 22 33"}
(1 row)
select unnest(array(select '11 22 33'::int2vector from generate_series(1,5)));
unnest
----------
11 22 33
11 22 33
11 22 33
11 22 33
11 22 33
(5 rows)
select pg_typeof(array(select '11 22 33'::oidvector from generate_series(1,5)));
pg_typeof
-------------
oidvector[]
(1 row)
select array(select '11 22 33'::oidvector from generate_series(1,5));
array
----------------------------------------------------------
{"11 22 33","11 22 33","11 22 33","11 22 33","11 22 33"}
(1 row)
select unnest(array(select '11 22 33'::oidvector from generate_series(1,5)));
unnest
----------
11 22 33
11 22 33
11 22 33
11 22 33
11 22 33
(5 rows)
-- array[] should do the same
select pg_typeof(array['11 22 33'::int2vector]);
pg_typeof
--------------
int2vector[]
(1 row)
select array['11 22 33'::int2vector];
array
--------------
{"11 22 33"}
(1 row)
select pg_typeof(unnest(array['11 22 33'::int2vector]));
pg_typeof
------------
int2vector
(1 row)
select unnest(array['11 22 33'::int2vector]);
unnest
----------
11 22 33
(1 row)
select pg_typeof(unnest('11 22 33'::int2vector));
pg_typeof
-----------
smallint
smallint
smallint
(3 rows)
select unnest('11 22 33'::int2vector);
unnest
--------
11
22
33
(3 rows)
select pg_typeof(array['11 22 33'::oidvector]);
pg_typeof
-------------
oidvector[]
(1 row)
select array['11 22 33'::oidvector];
array
--------------
{"11 22 33"}
(1 row)
select pg_typeof(unnest(array['11 22 33'::oidvector]));
pg_typeof
-----------
oidvector
(1 row)
select unnest(array['11 22 33'::oidvector]);
unnest
----------
11 22 33
(1 row)
select pg_typeof(unnest('11 22 33'::oidvector));
pg_typeof
-----------
oid
oid
oid
(3 rows)
select unnest('11 22 33'::oidvector);
unnest
--------
11
22
33
(3 rows)
-- Insert/update on a column that is array of composite
create temp table t1 (f1 int8_tbl[], distkey int4) distributed by (distkey);
insert into t1 (f1[5].q1) values(42);
select f1 from t1;
f1
-----------------
[5:5]={"(42,)"}
(1 row)
update t1 set f1[5].q2 = 43;
select f1 from t1;
f1
-------------------
[5:5]={"(42,43)"}
(1 row)
-- Check that arrays of composites are safely detoasted when needed
create temp table src (f1 text);
insert into src
select string_agg(random()::text,'') from generate_series(1,10000);
create type textandtext as (c1 text, c2 text);
create temp table dest (f1 textandtext[]);
insert into dest select array[row(f1,f1)::textandtext] from src;
select length(fipshash((f1[1]).c2)) from dest;
length
--------
32
(1 row)
delete from src;
select length(fipshash((f1[1]).c2)) from dest;
length
--------
32
(1 row)
truncate table src;
drop table src;
select length(fipshash((f1[1]).c2)) from dest;
length
--------
32
(1 row)
drop table dest;
drop type textandtext;
-- Tests for polymorphic-array form of width_bucket()
-- this exercises the varwidth and float8 code paths
SELECT
op,
width_bucket(op::numeric, ARRAY[1, 3, 5, 10.0]::numeric[]) AS wb_n1,
width_bucket(op::numeric, ARRAY[0, 5.5, 9.99]::numeric[]) AS wb_n2,
width_bucket(op::numeric, ARRAY[-6, -5, 2.0]::numeric[]) AS wb_n3,
width_bucket(op::float8, ARRAY[1, 3, 5, 10.0]::float8[]) AS wb_f1,
width_bucket(op::float8, ARRAY[0, 5.5, 9.99]::float8[]) AS wb_f2,
width_bucket(op::float8, ARRAY[-6, -5, 2.0]::float8[]) AS wb_f3
FROM (VALUES
(-5.2),
(-0.0000000001),
(0.000000000001),
(1),
(1.99999999999999),
(2),
(2.00000000000001),
(3),
(4),
(4.5),
(5),
(5.5),
(6),
(7),
(8),
(9),
(9.99999999999999),
(10),
(10.0000000000001)
) v(op);
op | wb_n1 | wb_n2 | wb_n3 | wb_f1 | wb_f2 | wb_f3
------------------+-------+-------+-------+-------+-------+-------
-5.2 | 0 | 0 | 1 | 0 | 0 | 1
-0.0000000001 | 0 | 0 | 2 | 0 | 0 | 2
0.000000000001 | 0 | 1 | 2 | 0 | 1 | 2
1 | 1 | 1 | 2 | 1 | 1 | 2
1.99999999999999 | 1 | 1 | 2 | 1 | 1 | 2
2 | 1 | 1 | 3 | 1 | 1 | 3
2.00000000000001 | 1 | 1 | 3 | 1 | 1 | 3
3 | 2 | 1 | 3 | 2 | 1 | 3
4 | 2 | 1 | 3 | 2 | 1 | 3
4.5 | 2 | 1 | 3 | 2 | 1 | 3
5 | 3 | 1 | 3 | 3 | 1 | 3
5.5 | 3 | 2 | 3 | 3 | 2 | 3
6 | 3 | 2 | 3 | 3 | 2 | 3
7 | 3 | 2 | 3 | 3 | 2 | 3
8 | 3 | 2 | 3 | 3 | 2 | 3
9 | 3 | 2 | 3 | 3 | 2 | 3
9.99999999999999 | 3 | 3 | 3 | 3 | 3 | 3
10 | 4 | 3 | 3 | 4 | 3 | 3
10.0000000000001 | 4 | 3 | 3 | 4 | 3 | 3
(19 rows)
-- ensure float8 path handles NaN properly
SELECT
op,
width_bucket(op, ARRAY[1, 3, 9, 'NaN', 'NaN']::float8[]) AS wb
FROM (VALUES
(-5.2::float8),
(4::float8),
(77::float8),
('NaN'::float8)
) v(op);
op | wb
------+----
-5.2 | 0
4 | 2
77 | 3
NaN | 5
(4 rows)
-- these exercise the generic fixed-width code path
SELECT
op,
width_bucket(op, ARRAY[1, 3, 5, 10]) AS wb_1
FROM generate_series(0,11) as op;
op | wb_1
----+------
0 | 0
1 | 1
2 | 1
3 | 2
4 | 2
5 | 3
6 | 3
7 | 3
8 | 3
9 | 3
10 | 4
11 | 4
(12 rows)
SELECT width_bucket(now(),
array['yesterday', 'today', 'tomorrow']::timestamptz[]);
width_bucket
--------------
2
(1 row)
-- corner cases
SELECT width_bucket(5, ARRAY[3]);
width_bucket
--------------
1
(1 row)
SELECT width_bucket(5, '{}');
width_bucket
--------------
0
(1 row)
-- error cases
SELECT width_bucket('5'::text, ARRAY[3, 4]::integer[]);
ERROR: function width_bucket(text, integer[]) does not exist
LINE 1: SELECT width_bucket('5'::text, ARRAY[3, 4]::integer[]);
^
HINT: No function matches the given name and argument types. You might need to add explicit type casts.
SELECT width_bucket(5, ARRAY[3, 4, NULL]);
ERROR: thresholds array must not contain NULLs
SELECT width_bucket(5, ARRAY[ARRAY[1, 2], ARRAY[3, 4]]);
ERROR: thresholds must be one-dimensional array
-- Suppress NOTICE messages when users/groups don't exist
SET client_min_messages TO 'error';
-- create a non-superuser
DROP ROLE IF EXISTS user_internal_stype;
CREATE USER user_internal_stype;
SET SESSION AUTHORIZATION user_internal_stype;
RESET client_min_messages;
-- Internal function for the aggregate
-- Is called for each item in an aggregation
CREATE FUNCTION int_agg_state (internal, int4)
RETURNS internal
AS 'array_agg_transfn'
LANGUAGE INTERNAL;
ERROR: permission denied for language internal
-- Internal function for the aggregate
-- Is called at the end of the aggregation, and returns an array.
CREATE FUNCTION int_agg_final_array (internal)
RETURNS int4[]
AS 'array_agg_finalfn'
LANGUAGE INTERNAL;
ERROR: permission denied for language internal
-- The aggregate function itself
-- uses the above functions to create an array of integers from an aggregation.
CREATE ORDERED AGGREGATE int_array_aggregate (
BASETYPE = int4,
SFUNC = int_agg_state,
STYPE = internal,
FINALFUNC = int_agg_final_array
);
ERROR: aggregate transition data type cannot be internal
-- change to superuser
-- start_ignore
\c -
-- end_ignore
-- Internal function for the aggregate
-- Is called for each item in an aggregation
CREATE FUNCTION int_agg_state (internal, int4)
RETURNS internal
AS 'array_agg_transfn'
LANGUAGE INTERNAL;
-- Internal function for the aggregate
-- Is called at the end of the aggregation, and returns an array.
CREATE FUNCTION int_agg_final_array (internal)
RETURNS int4[]
AS 'array_agg_finalfn'
LANGUAGE INTERNAL;
-- The aggregate function itself
-- uses the above functions to create an array of integers from an aggregation.
CREATE ORDERED AGGREGATE int_array_aggregate (
BASETYPE = int4,
SFUNC = int_agg_state,
STYPE = internal,
FINALFUNC = int_agg_final_array
);
-- Tests
select int_array_aggregate(i) from generate_series(1,10,2) i;
int_array_aggregate
---------------------
{1,3,5,7,9}
(1 row)
CREATE TEMP TABLE int_test_tbl1 AS (
SELECT id FROM generate_series(11, 1000, 11) as id
) DISTRIBUTED BY ( id );
CREATE TEMP TABLE int_test_tbl2 AS (
SELECT id FROM generate_series(7, 1000, 7) as id
) DISTRIBUTED BY ( id );
SELECT l.id as "number % 11 in left",
int_array_aggregate(r.id ORDER BY r.id) "array of those % 7 too in right"
FROM int_test_tbl1 l, int_test_tbl2 r
WHERE l.id % r.id = 0
GROUP BY l.id
ORDER BY l.id;
number % 11 in left | array of those % 7 too in right
---------------------+-------------------------------------------
77 | {7,77}
154 | {7,14,77,154}
231 | {7,21,77,231}
308 | {7,14,28,77,154,308}
385 | {7,35,77,385}
462 | {7,14,21,42,77,154,231,462}
539 | {7,49,77,539}
616 | {7,14,28,56,77,154,308,616}
693 | {7,21,63,77,231,693}
770 | {7,14,35,70,77,154,385,770}
847 | {7,77,847}
924 | {7,14,21,28,42,77,84,154,231,308,462,924}
(12 rows)
-- Array types are GPDB hashable
-- start_ignore
-- GPDB_12_MERGE_FIXME: Add an explicit COLLATE clause to cause ORCA to
-- fallback instead of adding an optimizer.out file. Re-visit and fix
-- when the collation work for ORCA is picked up again.
-- end_ignore
CREATE TEMP TABLE text_array_table (t text[]) DISTRIBUTED BY ( t );
INSERT INTO text_array_table VALUES ('{foo}' COLLATE "C");
CREATE TEMP TABLE int2_array_table (f1 int2[]) DISTRIBUTED BY (f1);
INSERT INTO int2_array_table VALUES ('{1,2,3}');
CREATE TEMP TABLE int4_array_table (f1 int4[]) DISTRIBUTED BY (f1);
INSERT INTO int4_array_table VALUES ('{1,2,3}');
CREATE TEMP TABLE int8_array_table (f1 int8[]) DISTRIBUTED BY (f1);
INSERT INTO int8_array_table VALUES ('{1,2,3}');
CREATE TEMP TABLE float4_array_table (f1 float4[]) DISTRIBUTED BY (f1);
INSERT INTO float4_array_table VALUES ('{1.1,2.1,3.1}');
CREATE TEMP TABLE float8_array_table (f1 float8[]) DISTRIBUTED BY (f1);
INSERT INTO float8_array_table VALUES ('{1.1,2.1,3.1}');
-- clean up
-- start_ignore
-- Drop above three functions
DROP AGGREGATE int_array_aggregate (int4);
DROP FUNCTION int_agg_final_array (internal);
DROP FUNCTION int_agg_state (internal, int4);
-- Drop user and group
--DROP ROLE IF EXISTS user_group;
RESET SESSION AUTHORIZATION;
DROP USER IF EXISTS user_internal_stype;
-- end_ignore
-- trim_array
SELECT arr, trim_array(arr, 2)
FROM
(VALUES ('{1,2,3,4,5,6}'::bigint[]),
('{1,2}'),
('[10:16]={1,2,3,4,5,6,7}'),
('[-15:-10]={1,2,3,4,5,6}'),
('{{1,10},{2,20},{3,30},{4,40}}')) v(arr);
arr | trim_array
-------------------------------+-----------------
{1,2,3,4,5,6} | {1,2,3,4}
{1,2} | {}
[10:16]={1,2,3,4,5,6,7} | {1,2,3,4,5}
[-15:-10]={1,2,3,4,5,6} | {1,2,3,4}
{{1,10},{2,20},{3,30},{4,40}} | {{1,10},{2,20}}
(5 rows)
SELECT trim_array(ARRAY[1, 2, 3], -1); -- fail
ERROR: number of elements to trim must be between 0 and 3
SELECT trim_array(ARRAY[1, 2, 3], 10); -- fail
ERROR: number of elements to trim must be between 0 and 3
SELECT trim_array(ARRAY[]::int[], 1); -- fail
ERROR: number of elements to trim must be between 0 and 0
-- array_shuffle
SELECT array_shuffle('{1,2,3,4,5,6}'::int[]) <@ '{1,2,3,4,5,6}';
?column?
----------
t
(1 row)
SELECT array_shuffle('{1,2,3,4,5,6}'::int[]) @> '{1,2,3,4,5,6}';
?column?
----------
t
(1 row)
SELECT array_dims(array_shuffle('[-1:2][2:3]={{1,2},{3,NULL},{5,6},{7,8}}'::int[]));
array_dims
-------------
[-1:2][2:3]
(1 row)
SELECT array_dims(array_shuffle('{{{1,2},{3,NULL}},{{5,6},{7,8}},{{9,10},{11,12}}}'::int[]));
array_dims
-----------------
[1:3][1:2][1:2]
(1 row)
-- array_sample
SELECT array_sample('{1,2,3,4,5,6}'::int[], 3) <@ '{1,2,3,4,5,6}';
?column?
----------
t
(1 row)
SELECT array_length(array_sample('{1,2,3,4,5,6}'::int[], 3), 1);
array_length
--------------
3
(1 row)
SELECT array_dims(array_sample('[-1:2][2:3]={{1,2},{3,NULL},{5,6},{7,8}}'::int[], 3));
array_dims
------------
[1:3][2:3]
(1 row)
SELECT array_dims(array_sample('{{{1,2},{3,NULL}},{{5,6},{7,8}},{{9,10},{11,12}}}'::int[], 2));
array_dims
-----------------
[1:2][1:2][1:2]
(1 row)
SELECT array_sample('{1,2,3,4,5,6}'::int[], -1); -- fail
ERROR: sample size must be between 0 and 6
SELECT array_sample('{1,2,3,4,5,6}'::int[], 7); --fail
ERROR: sample size must be between 0 and 6