sql/hstore.sql

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