PolarDB-for-PostgreSQL
352 строки · 15.1 Кб
1CREATE EXTENSION hstore;
2
3-- Check whether any of our opclasses fail amvalidate
4SELECT amname, opcname
5FROM pg_opclass opc LEFT JOIN pg_am am ON am.oid = opcmethod
6WHERE opc.oid >= 16384 AND NOT amvalidate(opc.oid);
7
8set escape_string_warning=off;
9
10--hstore;
11
12select ''::hstore;
13select 'a=>b'::hstore;
14select ' a=>b'::hstore;
15select 'a =>b'::hstore;
16select 'a=>b '::hstore;
17select 'a=> b'::hstore;
18select '"a"=>"b"'::hstore;
19select ' "a"=>"b"'::hstore;
20select '"a" =>"b"'::hstore;
21select '"a"=>"b" '::hstore;
22select '"a"=> "b"'::hstore;
23select 'aa=>bb'::hstore;
24select ' aa=>bb'::hstore;
25select 'aa =>bb'::hstore;
26select 'aa=>bb '::hstore;
27select 'aa=> bb'::hstore;
28select '"aa"=>"bb"'::hstore;
29select ' "aa"=>"bb"'::hstore;
30select '"aa" =>"bb"'::hstore;
31select '"aa"=>"bb" '::hstore;
32select '"aa"=> "bb"'::hstore;
33
34select 'aa=>bb, cc=>dd'::hstore;
35select 'aa=>bb , cc=>dd'::hstore;
36select 'aa=>bb ,cc=>dd'::hstore;
37select 'aa=>bb, "cc"=>dd'::hstore;
38select 'aa=>bb , "cc"=>dd'::hstore;
39select 'aa=>bb ,"cc"=>dd'::hstore;
40select 'aa=>"bb", cc=>dd'::hstore;
41select 'aa=>"bb" , cc=>dd'::hstore;
42select 'aa=>"bb" ,cc=>dd'::hstore;
43
44select 'aa=>null'::hstore;
45select 'aa=>NuLl'::hstore;
46select 'aa=>"NuLl"'::hstore;
47
48select e'\\=a=>q=w'::hstore;
49select e'"=a"=>q\\=w'::hstore;
50select e'"\\"a"=>q>w'::hstore;
51select e'\\"a=>q"w'::hstore;
52
53select ''::hstore;
54select ' '::hstore;
55
56-- -> operator
57
58select 'aa=>b, c=>d , b=>16'::hstore->'c';
59select 'aa=>b, c=>d , b=>16'::hstore->'b';
60select 'aa=>b, c=>d , b=>16'::hstore->'aa';
61select ('aa=>b, c=>d , b=>16'::hstore->'gg') is null;
62select ('aa=>NULL, c=>d , b=>16'::hstore->'aa') is null;
63select ('aa=>"NULL", c=>d , b=>16'::hstore->'aa') is null;
64
65-- -> array operator
66
67select 'aa=>"NULL", c=>d , b=>16'::hstore -> ARRAY['aa','c'];
68select 'aa=>"NULL", c=>d , b=>16'::hstore -> ARRAY['c','aa'];
69select 'aa=>NULL, c=>d , b=>16'::hstore -> ARRAY['aa','c',null];
70select 'aa=>1, c=>3, b=>2, d=>4'::hstore -> ARRAY[['b','d'],['aa','c']];
71
72-- exists/defined
73
74select exist('a=>NULL, b=>qq', 'a');
75select exist('a=>NULL, b=>qq', 'b');
76select exist('a=>NULL, b=>qq', 'c');
77select exist('a=>"NULL", b=>qq', 'a');
78select defined('a=>NULL, b=>qq', 'a');
79select defined('a=>NULL, b=>qq', 'b');
80select defined('a=>NULL, b=>qq', 'c');
81select defined('a=>"NULL", b=>qq', 'a');
82select hstore 'a=>NULL, b=>qq' ? 'a';
83select hstore 'a=>NULL, b=>qq' ? 'b';
84select hstore 'a=>NULL, b=>qq' ? 'c';
85select hstore 'a=>"NULL", b=>qq' ? 'a';
86select hstore 'a=>NULL, b=>qq' ?| ARRAY['a','b'];
87select hstore 'a=>NULL, b=>qq' ?| ARRAY['b','a'];
88select hstore 'a=>NULL, b=>qq' ?| ARRAY['c','a'];
89select hstore 'a=>NULL, b=>qq' ?| ARRAY['c','d'];
90select hstore 'a=>NULL, b=>qq' ?| '{}'::text[];
91select hstore 'a=>NULL, b=>qq' ?& ARRAY['a','b'];
92select hstore 'a=>NULL, b=>qq' ?& ARRAY['b','a'];
93select hstore 'a=>NULL, b=>qq' ?& ARRAY['c','a'];
94select hstore 'a=>NULL, b=>qq' ?& ARRAY['c','d'];
95select hstore 'a=>NULL, b=>qq' ?& '{}'::text[];
96
97-- delete
98
99select delete('a=>1 , b=>2, c=>3'::hstore, 'a');
100select delete('a=>null , b=>2, c=>3'::hstore, 'a');
101select delete('a=>1 , b=>2, c=>3'::hstore, 'b');
102select delete('a=>1 , b=>2, c=>3'::hstore, 'c');
103select delete('a=>1 , b=>2, c=>3'::hstore, 'd');
104select 'a=>1 , b=>2, c=>3'::hstore - 'a'::text;
105select 'a=>null , b=>2, c=>3'::hstore - 'a'::text;
106select 'a=>1 , b=>2, c=>3'::hstore - 'b'::text;
107select 'a=>1 , b=>2, c=>3'::hstore - 'c'::text;
108select 'a=>1 , b=>2, c=>3'::hstore - 'd'::text;
109select pg_column_size('a=>1 , b=>2, c=>3'::hstore - 'b'::text)
110= pg_column_size('a=>1, b=>2'::hstore);
111
112-- delete (array)
113
114select delete('a=>1 , b=>2, c=>3'::hstore, ARRAY['d','e']);
115select delete('a=>1 , b=>2, c=>3'::hstore, ARRAY['d','b']);
116select delete('a=>1 , b=>2, c=>3'::hstore, ARRAY['a','c']);
117select delete('a=>1 , b=>2, c=>3'::hstore, ARRAY[['b'],['c'],['a']]);
118select delete('a=>1 , b=>2, c=>3'::hstore, '{}'::text[]);
119select 'a=>1 , b=>2, c=>3'::hstore - ARRAY['d','e'];
120select 'a=>1 , b=>2, c=>3'::hstore - ARRAY['d','b'];
121select 'a=>1 , b=>2, c=>3'::hstore - ARRAY['a','c'];
122select 'a=>1 , b=>2, c=>3'::hstore - ARRAY[['b'],['c'],['a']];
123select 'a=>1 , b=>2, c=>3'::hstore - '{}'::text[];
124select pg_column_size('a=>1 , b=>2, c=>3'::hstore - ARRAY['a','c'])
125= pg_column_size('b=>2'::hstore);
126select pg_column_size('a=>1 , b=>2, c=>3'::hstore - '{}'::text[])
127= pg_column_size('a=>1, b=>2, c=>3'::hstore);
128
129-- delete (hstore)
130
131select delete('aa=>1 , b=>2, c=>3'::hstore, 'aa=>4, b=>2'::hstore);
132select delete('aa=>1 , b=>2, c=>3'::hstore, 'aa=>NULL, c=>3'::hstore);
133select delete('aa=>1 , b=>2, c=>3'::hstore, 'aa=>1, b=>2, c=>3'::hstore);
134select delete('aa=>1 , b=>2, c=>3'::hstore, 'b=>2'::hstore);
135select delete('aa=>1 , b=>2, c=>3'::hstore, ''::hstore);
136select 'aa=>1 , b=>2, c=>3'::hstore - 'aa=>4, b=>2'::hstore;
137select 'aa=>1 , b=>2, c=>3'::hstore - 'aa=>NULL, c=>3'::hstore;
138select 'aa=>1 , b=>2, c=>3'::hstore - 'aa=>1, b=>2, c=>3'::hstore;
139select 'aa=>1 , b=>2, c=>3'::hstore - 'b=>2'::hstore;
140select 'aa=>1 , b=>2, c=>3'::hstore - ''::hstore;
141select pg_column_size('a=>1 , b=>2, c=>3'::hstore - 'b=>2'::hstore)
142= pg_column_size('a=>1, c=>3'::hstore);
143select pg_column_size('a=>1 , b=>2, c=>3'::hstore - ''::hstore)
144= pg_column_size('a=>1, b=>2, c=>3'::hstore);
145
146-- ||
147select 'aa=>1 , b=>2, cq=>3'::hstore || 'cq=>l, b=>g, fg=>f';
148select 'aa=>1 , b=>2, cq=>3'::hstore || 'aq=>l';
149select 'aa=>1 , b=>2, cq=>3'::hstore || 'aa=>l';
150select 'aa=>1 , b=>2, cq=>3'::hstore || '';
151select ''::hstore || 'cq=>l, b=>g, fg=>f';
152select pg_column_size(''::hstore || ''::hstore) = pg_column_size(''::hstore);
153select pg_column_size('aa=>1'::hstore || 'b=>2'::hstore)
154= pg_column_size('aa=>1, b=>2'::hstore);
155select pg_column_size('aa=>1, b=>2'::hstore || ''::hstore)
156= pg_column_size('aa=>1, b=>2'::hstore);
157select pg_column_size(''::hstore || 'aa=>1, b=>2'::hstore)
158= pg_column_size('aa=>1, b=>2'::hstore);
159
160-- hstore(text,text)
161select 'a=>g, b=>c'::hstore || hstore('asd', 'gf');
162select 'a=>g, b=>c'::hstore || hstore('b', 'gf');
163select 'a=>g, b=>c'::hstore || hstore('b', 'NULL');
164select 'a=>g, b=>c'::hstore || hstore('b', NULL);
165select ('a=>g, b=>c'::hstore || hstore(NULL, 'b')) is null;
166select pg_column_size(hstore('b', 'gf'))
167= pg_column_size('b=>gf'::hstore);
168select pg_column_size('a=>g, b=>c'::hstore || hstore('b', 'gf'))
169= pg_column_size('a=>g, b=>gf'::hstore);
170
171-- slice()
172select slice(hstore 'aa=>1, b=>2, c=>3', ARRAY['g','h','i']);
173select slice(hstore 'aa=>1, b=>2, c=>3', ARRAY['c','b']);
174select slice(hstore 'aa=>1, b=>2, c=>3', ARRAY['aa','b']);
175select slice(hstore 'aa=>1, b=>2, c=>3', ARRAY['c','b','aa']);
176select pg_column_size(slice(hstore 'aa=>1, b=>2, c=>3', ARRAY['c','b']))
177= pg_column_size('b=>2, c=>3'::hstore);
178select pg_column_size(slice(hstore 'aa=>1, b=>2, c=>3', ARRAY['c','b','aa']))
179= pg_column_size('aa=>1, b=>2, c=>3'::hstore);
180
181-- array input
182select '{}'::text[]::hstore;
183select ARRAY['a','g','b','h','asd']::hstore;
184select ARRAY['a','g','b','h','asd','i']::hstore;
185select ARRAY[['a','g'],['b','h'],['asd','i']]::hstore;
186select ARRAY[['a','g','b'],['h','asd','i']]::hstore;
187select ARRAY[[['a','g'],['b','h'],['asd','i']]]::hstore;
188select hstore('{}'::text[]);
189select hstore(ARRAY['a','g','b','h','asd']);
190select hstore(ARRAY['a','g','b','h','asd','i']);
191select hstore(ARRAY[['a','g'],['b','h'],['asd','i']]);
192select hstore(ARRAY[['a','g','b'],['h','asd','i']]);
193select hstore(ARRAY[[['a','g'],['b','h'],['asd','i']]]);
194select hstore('[0:5]={a,g,b,h,asd,i}'::text[]);
195select hstore('[0:2][1:2]={{a,g},{b,h},{asd,i}}'::text[]);
196
197-- pairs of arrays
198select hstore(ARRAY['a','b','asd'], ARRAY['g','h','i']);
199select hstore(ARRAY['a','b','asd'], ARRAY['g','h',NULL]);
200select hstore(ARRAY['z','y','x'], ARRAY['1','2','3']);
201select hstore(ARRAY['aaa','bb','c','d'], ARRAY[null::text,null,null,null]);
202select hstore(ARRAY['aaa','bb','c','d'], null);
203select quote_literal(hstore('{}'::text[], '{}'::text[]));
204select quote_literal(hstore('{}'::text[], null));
205select hstore(ARRAY['a'], '{}'::text[]); -- error
206select hstore('{}'::text[], ARRAY['a']); -- error
207select pg_column_size(hstore(ARRAY['a','b','asd'], ARRAY['g','h','i']))
208= pg_column_size('a=>g, b=>h, asd=>i'::hstore);
209
210-- records
211select hstore(v) from (values (1, 'foo', 1.2, 3::float8)) v(a,b,c,d);
212create domain hstestdom1 as integer not null default 0;
213create table testhstore0 (a integer, b text, c numeric, d float8);
214create table testhstore1 (a integer, b text, c numeric, d float8, e hstestdom1);
215insert into testhstore0 values (1, 'foo', 1.2, 3::float8);
216insert into testhstore1 values (1, 'foo', 1.2, 3::float8);
217select hstore(v) from testhstore1 v;
218select hstore(null::testhstore0);
219select hstore(null::testhstore1);
220select pg_column_size(hstore(v))
221= pg_column_size('a=>1, b=>"foo", c=>"1.2", d=>"3", e=>"0"'::hstore)
222from testhstore1 v;
223select populate_record(v, hstore('c', '3.45')) from testhstore1 v;
224select populate_record(v, hstore('d', '3.45')) from testhstore1 v;
225select populate_record(v, hstore('e', '123')) from testhstore1 v;
226select populate_record(v, hstore('e', null)) from testhstore1 v;
227select populate_record(v, hstore('c', null)) from testhstore1 v;
228select populate_record(v, hstore('b', 'foo') || hstore('a', '123')) from testhstore1 v;
229select populate_record(v, hstore('b', 'foo') || hstore('e', null)) from testhstore0 v;
230select populate_record(v, hstore('b', 'foo') || hstore('e', null)) from testhstore1 v;
231select populate_record(v, '') from testhstore0 v;
232select populate_record(v, '') from testhstore1 v;
233select populate_record(null::testhstore1, hstore('c', '3.45') || hstore('a', '123'));
234select populate_record(null::testhstore1, hstore('c', '3.45') || hstore('e', '123'));
235select populate_record(null::testhstore0, '');
236select populate_record(null::testhstore1, '');
237select v #= hstore('c', '3.45') from testhstore1 v;
238select v #= hstore('d', '3.45') from testhstore1 v;
239select v #= hstore('e', '123') from testhstore1 v;
240select v #= hstore('c', null) from testhstore1 v;
241select v #= hstore('e', null) from testhstore0 v;
242select v #= hstore('e', null) from testhstore1 v;
243select v #= (hstore('b', 'foo') || hstore('a', '123')) from testhstore1 v;
244select v #= (hstore('b', 'foo') || hstore('e', '123')) from testhstore1 v;
245select v #= hstore '' from testhstore0 v;
246select v #= hstore '' from testhstore1 v;
247select null::testhstore1 #= (hstore('c', '3.45') || hstore('a', '123'));
248select null::testhstore1 #= (hstore('c', '3.45') || hstore('e', '123'));
249select null::testhstore0 #= hstore '';
250select null::testhstore1 #= hstore '';
251select v #= h from testhstore1 v, (values (hstore 'a=>123',1),('b=>foo,c=>3.21',2),('a=>null',3),('e=>123',4),('f=>blah',5)) x(h,i) order by i;
252
253-- keys/values
254select akeys('aa=>1 , b=>2, cq=>3'::hstore || 'cq=>l, b=>g, fg=>f');
255select akeys('""=>1');
256select akeys('');
257select avals('aa=>1 , b=>2, cq=>3'::hstore || 'cq=>l, b=>g, fg=>f');
258select avals('aa=>1 , b=>2, cq=>3'::hstore || 'cq=>l, b=>g, fg=>NULL');
259select avals('""=>1');
260select avals('');
261
262select hstore_to_array('aa=>1, cq=>l, b=>g, fg=>NULL'::hstore);
263select %% 'aa=>1, cq=>l, b=>g, fg=>NULL';
264
265select hstore_to_matrix('aa=>1, cq=>l, b=>g, fg=>NULL'::hstore);
266select %# 'aa=>1, cq=>l, b=>g, fg=>NULL';
267
268select * from skeys('aa=>1 , b=>2, cq=>3'::hstore || 'cq=>l, b=>g, fg=>f');
269select * from skeys('""=>1');
270select * from skeys('');
271select * from svals('aa=>1 , b=>2, cq=>3'::hstore || 'cq=>l, b=>g, fg=>f');
272select *, svals is null from svals('aa=>1 , b=>2, cq=>3'::hstore || 'cq=>l, b=>g, fg=>NULL');
273select * from svals('""=>1');
274select * from svals('');
275
276select * from each('aaa=>bq, b=>NULL, ""=>1 ');
277
278-- @>
279select 'a=>b, b=>1, c=>NULL'::hstore @> 'a=>b';
280select 'a=>b, b=>1, c=>NULL'::hstore @> 'a=>b, c=>NULL';
281select 'a=>b, b=>1, c=>NULL'::hstore @> 'a=>b, g=>NULL';
282select 'a=>b, b=>1, c=>NULL'::hstore @> 'g=>NULL';
283select 'a=>b, b=>1, c=>NULL'::hstore @> 'a=>c';
284select 'a=>b, b=>1, c=>NULL'::hstore @> 'a=>b';
285select 'a=>b, b=>1, c=>NULL'::hstore @> 'a=>b, c=>q';
286
287CREATE TABLE testhstore (h hstore);
288\copy testhstore from 'data/hstore.data'
289
290select count(*) from testhstore where h @> 'wait=>NULL';
291select count(*) from testhstore where h @> 'wait=>CC';
292select count(*) from testhstore where h @> 'wait=>CC, public=>t';
293select count(*) from testhstore where h ? 'public';
294select count(*) from testhstore where h ?| ARRAY['public','disabled'];
295select count(*) from testhstore where h ?& ARRAY['public','disabled'];
296
297create index hidx on testhstore using gist(h);
298set enable_seqscan=off;
299
300select count(*) from testhstore where h @> 'wait=>NULL';
301select count(*) from testhstore where h @> 'wait=>CC';
302select count(*) from testhstore where h @> 'wait=>CC, public=>t';
303select count(*) from testhstore where h ? 'public';
304select count(*) from testhstore where h ?| ARRAY['public','disabled'];
305select count(*) from testhstore where h ?& ARRAY['public','disabled'];
306
307drop index hidx;
308create index hidx on testhstore using gin (h);
309set enable_seqscan=off;
310
311select count(*) from testhstore where h @> 'wait=>NULL';
312select count(*) from testhstore where h @> 'wait=>CC';
313select count(*) from testhstore where h @> 'wait=>CC, public=>t';
314select count(*) from testhstore where h ? 'public';
315select count(*) from testhstore where h ?| ARRAY['public','disabled'];
316select count(*) from testhstore where h ?& ARRAY['public','disabled'];
317
318select count(*) from (select (each(h)).key from testhstore) as wow ;
319select key, count(*) from (select (each(h)).key from testhstore) as wow group by key order by count desc, key;
320
321-- sort/hash
322select count(distinct h) from testhstore;
323set enable_hashagg = false;
324select count(*) from (select h from (select * from testhstore union all select * from testhstore) hs group by h) hs2;
325set enable_hashagg = true;
326set enable_sort = false;
327select count(*) from (select h from (select * from testhstore union all select * from testhstore) hs group by h) hs2;
328select distinct * from (values (hstore '' || ''),('')) v(h);
329set enable_sort = true;
330
331-- btree
332drop index hidx;
333create index hidx on testhstore using btree (h);
334set enable_seqscan=off;
335
336select count(*) from testhstore where h #># 'p=>1';
337select count(*) from testhstore where h = 'pos=>98, line=>371, node=>CBA, indexed=>t';
338
339-- json and jsonb
340select hstore_to_json('"a key" =>1, b => t, c => null, d=> 12345, e => 012345, f=> 1.234, g=> 2.345e+4');
341select cast( hstore '"a key" =>1, b => t, c => null, d=> 12345, e => 012345, f=> 1.234, g=> 2.345e+4' as json);
342select hstore_to_json_loose('"a key" =>1, b => t, c => null, d=> 12345, e => 012345, f=> 1.234, g=> 2.345e+4, h=> "2016-01-01"');
343
344select hstore_to_jsonb('"a key" =>1, b => t, c => null, d=> 12345, e => 012345, f=> 1.234, g=> 2.345e+4');
345select cast( hstore '"a key" =>1, b => t, c => null, d=> 12345, e => 012345, f=> 1.234, g=> 2.345e+4' as jsonb);
346select hstore_to_jsonb_loose('"a key" =>1, b => t, c => null, d=> 12345, e => 012345, f=> 1.234, g=> 2.345e+4, h=> "2016-01-01"');
347
348create table test_json_agg (f1 text, f2 hstore);
349insert into test_json_agg values ('rec1','"a key" =>1, b => t, c => null, d=> 12345, e => 012345, f=> 1.234, g=> 2.345e+4'),
350('rec2','"a key" =>2, b => f, c => "null", d=> -12345, e => 012345.6, f=> -1.234, g=> 0.345e-4');
351select json_agg(q) from test_json_agg q;
352select json_agg(q) from (select f1, hstore_to_json_loose(f2) as f2 from test_json_agg) q;
353