14
-- only the 'e' array is 0-based, the others are 1-based.
16
INSERT INTO arrtest (a[1:5], b[1:1][1:2][1:2], c, d, f, g)
17
VALUES ('{1,2,3,4,5}', '{{{0,0},{1,2}}}', '{}', '{}', '{}', '{}');
18
UPDATE arrtest SET e[0] = '1.1';
19
UPDATE arrtest SET e[1] = '2.2';
20
INSERT INTO arrtest (f)
21
VALUES ('{"too long"}');
22
ERROR: value too long for type character(5)
23
INSERT INTO arrtest (a, b[1:2][1:2], c, d, e, f, g)
24
VALUES ('{11,12,23}', '{{3,4},{4,5}}', '{"foobar"}',
25
'{{"elt1", "elt2"}}', '{"3.4", "6.7"}',
26
'{"abc","abcde"}', '{"abc","abcde"}');
27
INSERT INTO arrtest (a, b[1:2], c, d[1:2])
28
VALUES ('{}', '{3,4}', '{foo,bar}', '{bar,foo}');
29
SELECT * FROM arrtest;
30
a | b | c | d | e | f | g
31
-------------+-----------------+-----------+---------------+-----------------+-----------------+-------------
32
{1,2,3,4,5} | {{{0,0},{1,2}}} | {} | {} | [0:1]={1.1,2.2} | {} | {}
33
{11,12,23} | {{3,4},{4,5}} | {foobar} | {{elt1,elt2}} | {3.4,6.7} | {"abc ",abcde} | {abc,abcde}
34
{} | {3,4} | {foo,bar} | {bar,foo} | | |
44
----+---+--------+------+-----
46
11 | | foobar | elt1 |
50
SELECT a[1], b[1][1][1], c[1], d[1][1], e[0]
53
----+---+--------+------+-----
55
11 | | foobar | elt1 |
65
------------+-----------------+-----------+---------------
66
{1,2,3} | {{{0,0},{1,2}}} | |
67
{11,12,23} | | {foobar} | {{elt1,elt2}}
71
SELECT array_dims(a) AS a,array_dims(b) AS b,array_dims(c) AS c
74
-------+-----------------+-------
75
[1:5] | [1:1][1:2][1:2] |
76
[1:3] | [1:2][1:2] | [1:1]
84
c = '{"foobar"}'::_name;
85
a | b | c | d | e | f | g
86
---+---+---+---+---+---+---
90
SET a[1:2] = '{16,25}'
91
WHERE NOT a = '{}'::_int2;
93
SET b[1:1][1:1][1:2] = '{113, 117}',
94
b[1:1][1:2][2:2] = '{142, 147}'
95
WHERE array_dims(b) = '[1:1][1:2][1:2]';
97
SET c[2:2] = '{"new_word"}'
98
WHERE array_dims(c) is not null;
99
SELECT a,b,c FROM arrtest;
101
---------------+-----------------------+-------------------
102
{16,25,3,4,5} | {{{113,142},{1,147}}} | {}
103
{} | {3,4} | {foo,new_word}
104
{16,25,23} | {{3,4},{4,5}} | {foobar,new_word}
113
------------+-----------------------+-------------------+----------
114
{16,25,3} | {{{113,142},{1,147}}} | |
116
{16,25,23} | | {foobar,new_word} | {{elt2}}
120
-- array expressions and operators
122
-- table creation and INSERTs
123
CREATE TEMP TABLE arrtest2 (i integer ARRAY[4], f float8[], n numeric[], t text[], d timestamp[]);
124
INSERT INTO arrtest2 VALUES(
125
ARRAY[[[113,142],[1,147]]],
126
ARRAY[1.1,1.2,1.3]::float8[],
128
ARRAY[[['aaa','aab'],['aba','abb'],['aca','acb']],[['baa','bab'],['bba','bbb'],['bca','bcb']]],
129
ARRAY['19620326','19931223','19970117']::timestamp[]
131
-- some more test data
132
CREATE TEMP TABLE arrtest_f (f0 int, f1 text, f2 float8);
133
insert into arrtest_f values(1,'cat1',1.21);
134
insert into arrtest_f values(2,'cat1',1.24);
135
insert into arrtest_f values(3,'cat1',1.18);
136
insert into arrtest_f values(4,'cat1',1.26);
137
insert into arrtest_f values(5,'cat1',1.15);
138
insert into arrtest_f values(6,'cat2',1.15);
139
insert into arrtest_f values(7,'cat2',1.26);
140
insert into arrtest_f values(8,'cat2',1.32);
141
insert into arrtest_f values(9,'cat2',1.30);
142
CREATE TEMP TABLE arrtest_i (f0 int, f1 text, f2 int);
143
insert into arrtest_i values(1,'cat1',21);
144
insert into arrtest_i values(2,'cat1',24);
145
insert into arrtest_i values(3,'cat1',18);
146
insert into arrtest_i values(4,'cat1',26);
147
insert into arrtest_i values(5,'cat1',15);
148
insert into arrtest_i values(6,'cat2',15);
149
insert into arrtest_i values(7,'cat2',26);
150
insert into arrtest_i values(8,'cat2',32);
151
insert into arrtest_i values(9,'cat2',30);
153
SELECT t.f[1][3][1] AS "131", t.f[2][2][1] AS "221" FROM (
154
SELECT ARRAY[[[111,112],[121,122],[131,132]],[[211,212],[221,122],[231,232]]] AS f
161
SELECT ARRAY[[[[[['hello'],['world']]]]]];
163
---------------------------
164
{{{{{{hello},{world}}}}}}
167
SELECT ARRAY[ARRAY['hello'],ARRAY['world']];
173
SELECT ARRAY(select f2 from arrtest_f order by f2) AS "ARRAY";
175
-----------------------------------------------
176
{1.15,1.15,1.18,1.21,1.24,1.26,1.26,1.3,1.32}
180
SELECT array_append(array[42], 6) AS "{42,6}";
186
SELECT array_prepend(6, array[42]) AS "{6,42}";
192
SELECT array_cat(ARRAY[1,2], ARRAY[3,4]) AS "{1,2,3,4}";
198
SELECT array_cat(ARRAY[1,2], ARRAY[[3,4],[5,6]]) AS "{{1,2},{3,4},{5,6}}";
200
--------------------------------
201
[0:2][1:2]={{1,2},{3,4},{5,6}}
204
SELECT array_cat(ARRAY[[3,4],[5,6]], ARRAY[1,2]) AS "{{3,4},{5,6},{1,2}}";
206
---------------------
211
SELECT a FROM arrtest WHERE b = ARRAY[[[113,142],[1,147]]];
217
SELECT NOT ARRAY[1.1,1.2,1.3] = ARRAY[1.1,1.2,1.3] AS "FALSE";
223
SELECT ARRAY[1,2] || 3 AS "{1,2,3}";
229
SELECT 0 || ARRAY[1,2] AS "{0,1,2}";
235
SELECT ARRAY[1,2] || ARRAY[3,4] AS "{1,2,3,4}";
241
SELECT ARRAY[[['hello','world']]] || ARRAY[[['happy','birthday']]] AS "ARRAY";
243
--------------------------------------
244
{{{hello,world}},{{happy,birthday}}}
247
SELECT ARRAY[[1,2],[3,4]] || ARRAY[5,6] AS "{{1,2},{3,4},{5,6}}";
249
---------------------
253
SELECT ARRAY[0,0] || ARRAY[1,1] || ARRAY[2,2] AS "{0,0,1,1,2,2}";
259
SELECT 0 || ARRAY[1,2] || 3 AS "{0,1,2,3}";
266
SELECT ARRAY[1,2,3]::text[]::int[]::float8[] AS "{1,2,3}";
272
SELECT ARRAY[1,2,3]::text[]::int[]::float8[] is of (float8[]) as "TRUE";
278
SELECT ARRAY[['a','bc'],['def','hijk']]::text[]::varchar[] AS "{{a,bc},{def,hijk}}";
280
---------------------
284
SELECT ARRAY[['a','bc'],['def','hijk']]::text[]::varchar[] is of (varchar[]) as "TRUE";
290
SELECT CAST(ARRAY[[[[[['a','bb','ccc']]]]]] as text[]) as "{{{{{{a,bb,ccc}}}}}}";
292
----------------------
296
-- scalar op any/all (array)
297
select 33 = any ('{1,2,3}');
303
select 33 = any ('{1,2,33}');
309
select 33 = all ('{1,2,33}');
315
select 33 >= all ('{1,2,33}');
322
select null::int >= all ('{1,2,33}');
328
select null::int >= all ('{}');
334
select null::int >= any ('{}');
341
select 33.4 = any (array[1,2,3]);
347
select 33.4 > all (array[1,2,3]);
354
select 33 * any ('{1,2,3}');
355
ERROR: op ANY/ALL (array) requires operator to yield boolean
356
select 33 * any (44);
357
ERROR: op ANY/ALL (array) requires array on right side
358
-- test indexes on arrays
359
create temp table arr_tbl (f1 int[] unique);
360
NOTICE: CREATE TABLE / UNIQUE will create implicit index "arr_tbl_f1_key" for table "arr_tbl"
361
insert into arr_tbl values ('{1,2,3}');
362
insert into arr_tbl values ('{1,2}');
364
insert into arr_tbl values ('{1,2,3}');
365
ERROR: duplicate key violates unique constraint "arr_tbl_f1_key"
366
insert into arr_tbl values ('{2,3,4}');
367
insert into arr_tbl values ('{1,5,3}');
368
insert into arr_tbl values ('{1,2,10}');
369
set enable_seqscan to off;
370
select * from arr_tbl where f1 > '{1,2,3}' and f1 <= '{1,5,3}';
377
-- note: if above select doesn't produce the expected tuple order,
378
-- then you didn't get an indexscan plan, and something is busted.
379
-- test [not] (like|ilike) (any|all) (...)
380
select 'foo' like any (array['%a', '%o']); -- t
386
select 'foo' like any (array['%a', '%b']); -- f
392
select 'foo' like all (array['f%', '%o']); -- t
398
select 'foo' like all (array['f%', '%b']); -- f
404
select 'foo' not like any (array['%a', '%b']); -- t
410
select 'foo' not like all (array['%a', '%o']); -- f
416
select 'foo' ilike any (array['%A', '%O']); -- t
422
select 'foo' ilike all (array['F%', '%O']); -- t
429
-- General array parser tests
431
-- none of the following should be accepted
432
select '{{1,{2}},{2,3}}'::text[];
433
ERROR: malformed array literal: "{{1,{2}},{2,3}}"
434
select '{{},{}}'::text[];
435
ERROR: malformed array literal: "{{},{}}"
436
select '{{1,2},\\{2,3}}'::text[];
437
ERROR: malformed array literal: "{{1,2},\{2,3}}"
438
select '{{"1 2" x},{3}}'::text[];
439
ERROR: malformed array literal: "{{"1 2" x},{3}}"
440
select '{}}'::text[];
441
ERROR: malformed array literal: "{}}"
442
select '{ }}'::text[];
443
ERROR: malformed array literal: "{ }}"
444
-- none of the above should be accepted
445
-- all of the following should be accepted
452
select '{{{1,2,3,4},{2,3,4,5}},{{3,4,5,6},{4,5,6,7}}}'::text[];
454
-----------------------------------------------
455
{{{1,2,3,4},{2,3,4,5}},{{3,4,5,6},{4,5,6,7}}}
458
select '{0 second ,0 second}'::interval[];
464
select '{ { "," } , { 3 } }'::text[];
470
select ' { { " 0 second " , 0 second } }'::text[];
472
-------------------------------
473
{{" 0 second ","0 second"}}
478
@ 1 hour @ 42 minutes @ 20 seconds
481
------------------------------------
482
{"@ 0","@ 1 hour 42 mins 20 secs"}
485
-- all of the above should be accepted