wuxiaojun
/
openGauss-server
forked from huawei/openGauss-server
1
0
Fork 0
Code Issues Pull Requests Projects Releases Wiki Activity
openGauss-server/contrib/hstore/expected/hstore.out

1089 lines
24 KiB
Plaintext
Raw Permalink Blame History

CREATE EXTENSION hstore;
set escape_string_warning=off;
--hstore;
select ''::hstore;
hstore
--------
(1 row)
select 'a=>b'::hstore;
hstore
----------
"a"=>"b"
(1 row)
select ' a=>b'::hstore;
hstore
----------
"a"=>"b"
(1 row)
select 'a =>b'::hstore;
hstore
----------
"a"=>"b"
(1 row)
select 'a=>b '::hstore;
hstore
----------
"a"=>"b"
(1 row)
select 'a=> b'::hstore;
hstore
----------
"a"=>"b"
(1 row)
select '"a"=>"b"'::hstore;
hstore
----------
"a"=>"b"
(1 row)
select ' "a"=>"b"'::hstore;
hstore
----------
"a"=>"b"
(1 row)
select '"a" =>"b"'::hstore;
hstore
----------
"a"=>"b"
(1 row)
select '"a"=>"b" '::hstore;
hstore
----------
"a"=>"b"
(1 row)
select '"a"=> "b"'::hstore;
hstore
----------
"a"=>"b"
(1 row)
select 'aa=>bb'::hstore;
hstore
------------
"aa"=>"bb"
(1 row)
select ' aa=>bb'::hstore;
hstore
------------
"aa"=>"bb"
(1 row)
select 'aa =>bb'::hstore;
hstore
------------
"aa"=>"bb"
(1 row)
select 'aa=>bb '::hstore;
hstore
------------
"aa"=>"bb"
(1 row)
select 'aa=> bb'::hstore;
hstore
------------
"aa"=>"bb"
(1 row)
select '"aa"=>"bb"'::hstore;
hstore
------------
"aa"=>"bb"
(1 row)
select ' "aa"=>"bb"'::hstore;
hstore
------------
"aa"=>"bb"
(1 row)
select '"aa" =>"bb"'::hstore;
hstore
------------
"aa"=>"bb"
(1 row)
select '"aa"=>"bb" '::hstore;
hstore
------------
"aa"=>"bb"
(1 row)
select '"aa"=> "bb"'::hstore;
hstore
------------
"aa"=>"bb"
(1 row)
select 'aa=>bb, cc=>dd'::hstore;
hstore
------------------------
"aa"=>"bb", "cc"=>"dd"
(1 row)
select 'aa=>bb , cc=>dd'::hstore;
hstore
------------------------
"aa"=>"bb", "cc"=>"dd"
(1 row)
select 'aa=>bb ,cc=>dd'::hstore;
hstore
------------------------
"aa"=>"bb", "cc"=>"dd"
(1 row)
select 'aa=>bb, "cc"=>dd'::hstore;
hstore
------------------------
"aa"=>"bb", "cc"=>"dd"
(1 row)
select 'aa=>bb , "cc"=>dd'::hstore;
hstore
------------------------
"aa"=>"bb", "cc"=>"dd"
(1 row)
select 'aa=>bb ,"cc"=>dd'::hstore;
hstore
------------------------
"aa"=>"bb", "cc"=>"dd"
(1 row)
select 'aa=>"bb", cc=>dd'::hstore;
hstore
------------------------
"aa"=>"bb", "cc"=>"dd"
(1 row)
select 'aa=>"bb" , cc=>dd'::hstore;
hstore
------------------------
"aa"=>"bb", "cc"=>"dd"
(1 row)
select 'aa=>"bb" ,cc=>dd'::hstore;
hstore
------------------------
"aa"=>"bb", "cc"=>"dd"
(1 row)
select 'aa=>null'::hstore;
hstore
------------
"aa"=>NULL
(1 row)
select 'aa=>NuLl'::hstore;
hstore
------------
"aa"=>NULL
(1 row)
select 'aa=>"NuLl"'::hstore;
hstore
--------------
"aa"=>"NuLl"
(1 row)
select e'\\=a=>q=w'::hstore;
hstore
-------------
"=a"=>"q=w"
(1 row)
select e'"=a"=>q\\=w'::hstore;
hstore
-------------
"=a"=>"q=w"
(1 row)
select e'"\\"a"=>q>w'::hstore;
hstore
--------------
"\"a"=>"q>w"
(1 row)
select e'\\"a=>q"w'::hstore;
hstore
---------------
"\"a"=>"q\"w"
(1 row)
select ''::hstore;
hstore
--------
(1 row)
select ' '::hstore;
hstore
--------
(1 row)
-- -> operator
select fetchval('aa=>b, c=>d , b=>16'::hstore, 'c');
fetchval
----------
d
(1 row)
select fetchval('aa=>b, c=>d , b=>16'::hstore, 'b');
fetchval
----------
16
(1 row)
select fetchval('aa=>b, c=>d , b=>16'::hstore, 'aa');
fetchval
----------
b
(1 row)
select (fetchval('aa=>b, c=>d , b=>16'::hstore, 'gg')) is null;
?column?
----------
t
(1 row)
select (fetchval('aa=>NULL, c=>d , b=>16'::hstore, 'aa')) is null;
?column?
----------
t
(1 row)
select (fetchval('aa=>"NULL", c=>d , b=>16'::hstore, 'aa')) is null;
?column?
----------
f
(1 row)
-- -> array operator
select slice_array('aa=>"NULL", c=>d , b=>16'::hstore, ARRAY['aa','c']);
slice_array
-------------
{"NULL",d}
(1 row)
select slice_array('aa=>"NULL", c=>d , b=>16'::hstore, ARRAY['c','aa']);
slice_array
-------------
{d,"NULL"}
(1 row)
select slice_array('aa=>NULL, c=>d , b=>16'::hstore, ARRAY['aa','c',null]);
slice_array
---------------
{NULL,d,NULL}
(1 row)
select slice_array('aa=>1, c=>3, b=>2, d=>4'::hstore, ARRAY[['b','d'],['aa','c']]);
slice_array
---------------
{{2,4},{1,3}}
(1 row)
-- exists/defined
select exist('a=>NULL, b=>qq', 'a');
exist
-------
t
(1 row)
select exist('a=>NULL, b=>qq', 'b');
exist
-------
t
(1 row)
select exist('a=>NULL, b=>qq', 'c');
exist
-------
f
(1 row)
select exist('a=>"NULL", b=>qq', 'a');
exist
-------
t
(1 row)
select defined('a=>NULL, b=>qq', 'a');
defined
---------
f
(1 row)
select defined('a=>NULL, b=>qq', 'b');
defined
---------
t
(1 row)
select defined('a=>NULL, b=>qq', 'c');
defined
---------
f
(1 row)
select defined('a=>"NULL", b=>qq', 'a');
defined
---------
t
(1 row)
/*
select hstore 'a=>NULL, b=>qq' ? 'a';
select hstore 'a=>NULL, b=>qq' ? 'b';
select hstore 'a=>NULL, b=>qq' ? 'c';
select hstore 'a=>"NULL", b=>qq' ? 'a';
select hstore 'a=>NULL, b=>qq' ?| ARRAY['a','b'];
select hstore 'a=>NULL, b=>qq' ?| ARRAY['b','a'];
select hstore 'a=>NULL, b=>qq' ?| ARRAY['c','a'];
select hstore 'a=>NULL, b=>qq' ?| ARRAY['c','d'];
select hstore 'a=>NULL, b=>qq' ?| '{}'::text[];
select hstore 'a=>NULL, b=>qq' ?& ARRAY['a','b'];
select hstore 'a=>NULL, b=>qq' ?& ARRAY['b','a'];
select hstore 'a=>NULL, b=>qq' ?& ARRAY['c','a'];
select hstore 'a=>NULL, b=>qq' ?& ARRAY['c','d'];
select hstore 'a=>NULL, b=>qq' ?& '{}'::text[];
*/
-- delete
select delete('a=>1 , b=>2, c=>3'::hstore, 'a');
delete
--------------------
"b"=>"2", "c"=>"3"
(1 row)
select delete('a=>null , b=>2, c=>3'::hstore, 'a');
delete
--------------------
"b"=>"2", "c"=>"3"
(1 row)
select delete('a=>1 , b=>2, c=>3'::hstore, 'b');
delete
--------------------
"a"=>"1", "c"=>"3"
(1 row)
select delete('a=>1 , b=>2, c=>3'::hstore, 'c');
delete
--------------------
"a"=>"1", "b"=>"2"
(1 row)
select delete('a=>1 , b=>2, c=>3'::hstore, 'd');
delete
------------------------------
"a"=>"1", "b"=>"2", "c"=>"3"
(1 row)
/*
select 'a=>1 , b=>2, c=>3'::hstore - 'a'::text;
select 'a=>null , b=>2, c=>3'::hstore - 'a'::text;
select 'a=>1 , b=>2, c=>3'::hstore - 'b'::text;
select 'a=>1 , b=>2, c=>3'::hstore - 'c'::text;
select 'a=>1 , b=>2, c=>3'::hstore - 'd'::text;
select pg_column_size('a=>1 , b=>2, c=>3'::hstore - 'b'::text)
= pg_column_size('a=>1, b=>2'::hstore);
*/
select pg_column_size(delete('a=>1 , b=>2, c=>3'::hstore, 'b'::text))
= pg_column_size('a=>1, b=>2'::hstore);
?column?
----------
t
(1 row)
-- delete (array)
select delete('a=>1 , b=>2, c=>3'::hstore, ARRAY['d','e']);
delete
------------------------------
"a"=>"1", "b"=>"2", "c"=>"3"
(1 row)
select delete('a=>1 , b=>2, c=>3'::hstore, ARRAY['d','b']);
delete
--------------------
"a"=>"1", "c"=>"3"
(1 row)
select delete('a=>1 , b=>2, c=>3'::hstore, ARRAY['a','c']);
delete
----------
"b"=>"2"
(1 row)
select delete('a=>1 , b=>2, c=>3'::hstore, ARRAY[['b'],['c'],['a']]);
delete
--------
(1 row)
select delete('a=>1 , b=>2, c=>3'::hstore, '{}'::text[]);
delete
------------------------------
"a"=>"1", "b"=>"2", "c"=>"3"
(1 row)
/*
select 'a=>1 , b=>2, c=>3'::hstore - ARRAY['d','e'];
select 'a=>1 , b=>2, c=>3'::hstore - ARRAY['d','b'];
select 'a=>1 , b=>2, c=>3'::hstore - ARRAY['a','c'];
select 'a=>1 , b=>2, c=>3'::hstore - ARRAY[['b'],['c'],['a']];
select 'a=>1 , b=>2, c=>3'::hstore - '{}'::text[];
select pg_column_size('a=>1 , b=>2, c=>3'::hstore - ARRAY['a','c'])
= pg_column_size('b=>2'::hstore);
select pg_column_size('a=>1 , b=>2, c=>3'::hstore - '{}'::text[])
= pg_column_size('a=>1, b=>2, c=>3'::hstore);
*/
select pg_column_size(delete('a=>1 , b=>2, c=>3'::hstore, ARRAY['a','c']))
= pg_column_size('b=>2'::hstore);
?column?
----------
t
(1 row)
select pg_column_size(delete('a=>1 , b=>2, c=>3'::hstore, '{}'::text[]))
= pg_column_size('a=>1, b=>2, c=>3'::hstore);
?column?
----------
t
(1 row)
-- delete (hstore)
select delete('aa=>1 , b=>2, c=>3'::hstore, 'aa=>4, b=>2'::hstore);
delete
---------------------
"c"=>"3", "aa"=>"1"
(1 row)
select delete('aa=>1 , b=>2, c=>3'::hstore, 'aa=>NULL, c=>3'::hstore);
delete
---------------------
"b"=>"2", "aa"=>"1"
(1 row)
select delete('aa=>1 , b=>2, c=>3'::hstore, 'aa=>1, b=>2, c=>3'::hstore);
delete
--------
(1 row)
select delete('aa=>1 , b=>2, c=>3'::hstore, 'b=>2'::hstore);
delete
---------------------
"c"=>"3", "aa"=>"1"
(1 row)
select delete('aa=>1 , b=>2, c=>3'::hstore, ''::hstore);
delete
--------
(1 row)
/*
select 'aa=>1 , b=>2, c=>3'::hstore - 'aa=>4, b=>2'::hstore;
select 'aa=>1 , b=>2, c=>3'::hstore - 'aa=>NULL, c=>3'::hstore;
select 'aa=>1 , b=>2, c=>3'::hstore - 'aa=>1, b=>2, c=>3'::hstore;
select 'aa=>1 , b=>2, c=>3'::hstore - 'b=>2'::hstore;
select 'aa=>1 , b=>2, c=>3'::hstore - ''::hstore;
select pg_column_size('a=>1 , b=>2, c=>3'::hstore - 'b=>2'::hstore)
= pg_column_size('a=>1, c=>3'::hstore);
select pg_column_size('a=>1 , b=>2, c=>3'::hstore - ''::hstore)
= pg_column_size('a=>1, b=>2, c=>3'::hstore);
*/
select pg_column_size(delete('a=>1 , b=>2, c=>3'::hstore, 'b=>2'::hstore))
= pg_column_size('a=>1, c=>3'::hstore);
?column?
----------
t
(1 row)
select pg_column_size(delete('a=>1 , b=>2, c=>3'::hstore, ''::hstore))
= pg_column_size('a=>1, b=>2, c=>3'::hstore);
?column?
----------
(1 row)
-- ||
/*
select 'aa=>1 , b=>2, cq=>3'::hstore || 'cq=>l, b=>g, fg=>f';
select 'aa=>1 , b=>2, cq=>3'::hstore || 'aq=>l';
select 'aa=>1 , b=>2, cq=>3'::hstore || 'aa=>l';
select 'aa=>1 , b=>2, cq=>3'::hstore || '';
select ''::hstore || 'cq=>l, b=>g, fg=>f';
select pg_column_size(''::hstore || ''::hstore) = pg_column_size(''::hstore);
select pg_column_size('aa=>1'::hstore || 'b=>2'::hstore)
= pg_column_size('aa=>1, b=>2'::hstore);
select pg_column_size('aa=>1, b=>2'::hstore || ''::hstore)
= pg_column_size('aa=>1, b=>2'::hstore);
select pg_column_size(''::hstore || 'aa=>1, b=>2'::hstore)
= pg_column_size('aa=>1, b=>2'::hstore);
-- hstore(text,text)
select 'a=>g, b=>c'::hstore || hstore('asd', 'gf');
select 'a=>g, b=>c'::hstore || hstore('b', 'gf');
select 'a=>g, b=>c'::hstore || hstore('b', 'NULL');
select 'a=>g, b=>c'::hstore || hstore('b', NULL);
select ('a=>g, b=>c'::hstore || hstore(NULL, 'b')) is null;
select pg_column_size(hstore('b', 'gf'))
= pg_column_size('b=>gf'::hstore);
select pg_column_size('a=>g, b=>c'::hstore || hstore('b', 'gf'))
= pg_column_size('a=>g, b=>gf'::hstore);
*/
-- hs_concat
select hs_concat('aa=>1 , b=>2, cq=>3'::hstore, 'cq=>l, b=>g, fg=>f');
hs_concat
-------------------------------------------
"b"=>"g", "aa"=>"1", "cq"=>"l", "fg"=>"f"
(1 row)
select hs_concat('aa=>1 , b=>2, cq=>3'::hstore, 'aq=>l');
hs_concat
-------------------------------------------
"b"=>"2", "aa"=>"1", "aq"=>"l", "cq"=>"3"
(1 row)
select hs_concat('aa=>1 , b=>2, cq=>3'::hstore, 'aa=>l');
hs_concat
--------------------------------
"b"=>"2", "aa"=>"l", "cq"=>"3"
(1 row)
select hs_concat('aa=>1 , b=>2, cq=>3'::hstore, '');
hs_concat
--------------------------------
"b"=>"2", "aa"=>"1", "cq"=>"3"
(1 row)
select hs_concat(''::hstore, 'cq=>l, b=>g, fg=>f');
hs_concat
--------------------------------
"b"=>"g", "cq"=>"l", "fg"=>"f"
(1 row)
select pg_column_size(hs_concat(''::hstore, ''::hstore)) = pg_column_size(''::hstore);
?column?
----------
(1 row)
select pg_column_size(hs_concat('aa=>1'::hstore, 'b=>2'::hstore))
= pg_column_size('aa=>1, b=>2'::hstore);
?column?
----------
t
(1 row)
select pg_column_size(hs_concat('aa=>1, b=>2'::hstore, ''::hstore))
= pg_column_size('aa=>1, b=>2'::hstore);
?column?
----------
t
(1 row)
select pg_column_size(hs_concat(''::hstore, 'aa=>1, b=>2'::hstore))
= pg_column_size('aa=>1, b=>2'::hstore);
?column?
----------
t
(1 row)
-- hstore(text,text)
select hs_concat('a=>g, b=>c'::hstore, hstore('asd', 'gf'));
hs_concat
---------------------------------
"a"=>"g", "b"=>"c", "asd"=>"gf"
(1 row)
select hs_concat('a=>g, b=>c'::hstore, hstore('b', 'gf'));
hs_concat
---------------------
"a"=>"g", "b"=>"gf"
(1 row)
select hs_concat('a=>g, b=>c'::hstore, hstore('b', 'NULL'));
hs_concat
-----------------------
"a"=>"g", "b"=>"NULL"
(1 row)
select hs_concat('a=>g, b=>c'::hstore, hstore('b', NULL));
hs_concat
---------------------
"a"=>"g", "b"=>NULL
(1 row)
select (hs_concat('a=>g, b=>c'::hstore, hstore(NULL, 'b'))) is null;
?column?
----------
f
(1 row)
select pg_column_size(hstore('b', 'gf'))
= pg_column_size('b=>gf'::hstore);
?column?
----------
t
(1 row)
select pg_column_size(hs_concat('a=>g, b=>c'::hstore, hstore('b', 'gf')))
= pg_column_size('a=>g, b=>gf'::hstore);
?column?
----------
t
(1 row)
-- slice()
select slice(hstore 'aa=>1, b=>2, c=>3', ARRAY['g','h','i']);
slice
-------
(1 row)
select slice(hstore 'aa=>1, b=>2, c=>3', ARRAY['c','b']);
slice
--------------------
"b"=>"2", "c"=>"3"
(1 row)
select slice(hstore 'aa=>1, b=>2, c=>3', ARRAY['aa','b']);
slice
---------------------
"b"=>"2", "aa"=>"1"
(1 row)
select slice(hstore 'aa=>1, b=>2, c=>3', ARRAY['c','b','aa']);
slice
-------------------------------
"b"=>"2", "c"=>"3", "aa"=>"1"
(1 row)
select pg_column_size(slice(hstore 'aa=>1, b=>2, c=>3', ARRAY['c','b']))
= pg_column_size('b=>2, c=>3'::hstore);
?column?
----------
t
(1 row)
select pg_column_size(slice(hstore 'aa=>1, b=>2, c=>3', ARRAY['c','b','aa']))
= pg_column_size('aa=>1, b=>2, c=>3'::hstore);
?column?
----------
t
(1 row)
-- array input
select '{}'::text[]::hstore;
hstore
--------
(1 row)
select ARRAY['a','g','b','h','asd']::hstore;
ERROR: array must have even number of elements
select ARRAY['a','g','b','h','asd','i']::hstore;
array
--------------------------------
"a"=>"g", "b"=>"h", "asd"=>"i"
(1 row)
select ARRAY[['a','g'],['b','h'],['asd','i']]::hstore;
array
--------------------------------
"a"=>"g", "b"=>"h", "asd"=>"i"
(1 row)
select ARRAY[['a','g','b'],['h','asd','i']]::hstore;
ERROR: array must have two columns
select ARRAY[[['a','g'],['b','h'],['asd','i']]]::hstore;
ERROR: wrong number of array subscripts
select hstore('{}'::text[]);
hstore
--------
(1 row)
select hstore(ARRAY['a','g','b','h','asd']);
ERROR: array must have even number of elements
select hstore(ARRAY['a','g','b','h','asd','i']);
hstore
--------------------------------
"a"=>"g", "b"=>"h", "asd"=>"i"
(1 row)
select hstore(ARRAY[['a','g'],['b','h'],['asd','i']]);
hstore
--------------------------------
"a"=>"g", "b"=>"h", "asd"=>"i"
(1 row)
select hstore(ARRAY[['a','g','b'],['h','asd','i']]);
ERROR: array must have two columns
select hstore(ARRAY[[['a','g'],['b','h'],['asd','i']]]);
ERROR: wrong number of array subscripts
select hstore('[0:5]={a,g,b,h,asd,i}'::text[]);
hstore
--------------------------------
"a"=>"g", "b"=>"h", "asd"=>"i"
(1 row)
select hstore('[0:2][1:2]={{a,g},{b,h},{asd,i}}'::text[]);
hstore
--------------------------------
"a"=>"g", "b"=>"h", "asd"=>"i"
(1 row)
-- pairs of arrays
select hstore(ARRAY['a','b','asd'], ARRAY['g','h','i']);
hstore
--------------------------------
"a"=>"g", "b"=>"h", "asd"=>"i"
(1 row)
select hstore(ARRAY['a','b','asd'], ARRAY['g','h',NULL]);
hstore
---------------------------------
"a"=>"g", "b"=>"h", "asd"=>NULL
(1 row)
select hstore(ARRAY['z','y','x'], ARRAY['1','2','3']);
hstore
------------------------------
"x"=>"3", "y"=>"2", "z"=>"1"
(1 row)
select hstore(ARRAY['aaa','bb','c','d'], ARRAY[null::text,null,null,null]);
hstore
-----------------------------------------------
"c"=>NULL, "d"=>NULL, "bb"=>NULL, "aaa"=>NULL
(1 row)
select hstore(ARRAY['aaa','bb','c','d'], null);
hstore
-----------------------------------------------
"c"=>NULL, "d"=>NULL, "bb"=>NULL, "aaa"=>NULL
(1 row)
select quote_literal(hstore('{}'::text[], '{}'::text[]));
quote_literal
---------------
''
(1 row)
select quote_literal(hstore('{}'::text[], null));
quote_literal
---------------
''
(1 row)
select hstore(ARRAY['a'], '{}'::text[]); -- error
ERROR: arrays must have same bounds
select hstore('{}'::text[], ARRAY['a']); -- error
ERROR: arrays must have same bounds
select pg_column_size(hstore(ARRAY['a','b','asd'], ARRAY['g','h','i']))
= pg_column_size('a=>g, b=>h, asd=>i'::hstore);
?column?
----------
t
(1 row)
/*
-- records
select hstore(v) from (values (1, 'foo', 1.2, 3::float8)) v(a,b,c,d);
create domain hstestdom1 as integer not null default 0;
create table testhstore0 (a integer, b text, c numeric, d float8);
create table testhstore1 (a integer, b text, c numeric, d float8, e hstestdom1);
insert into testhstore0 values (1, 'foo', 1.2, 3::float8);
insert into testhstore1 values (1, 'foo', 1.2, 3::float8);
select hstore(v) from testhstore1 v;
select hstore(null::testhstore0);
select hstore(null::testhstore1);
select pg_column_size(hstore(v))
= pg_column_size('a=>1, b=>"foo", c=>"1.2", d=>"3", e=>"0"'::hstore)
from testhstore1 v;
select populate_record(v, hstore('c', '3.45')) from testhstore1 v;
select populate_record(v, hstore('d', '3.45')) from testhstore1 v;
select populate_record(v, hstore('e', '123')) from testhstore1 v;
select populate_record(v, hstore('e', null)) from testhstore1 v;
select populate_record(v, hstore('c', null)) from testhstore1 v;
select populate_record(v, hstore('b', 'foo') || hstore('a', '123')) from testhstore1 v;
select populate_record(v, hstore('b', 'foo') || hstore('e', null)) from testhstore0 v;
select populate_record(v, hstore('b', 'foo') || hstore('e', null)) from testhstore1 v;
select populate_record(v, '') from testhstore0 v;
select populate_record(v, '') from testhstore1 v;
select populate_record(null::testhstore1, hstore('c', '3.45') || hstore('a', '123'));
select populate_record(null::testhstore1, hstore('c', '3.45') || hstore('e', '123'));
select populate_record(null::testhstore0, '');
select populate_record(null::testhstore1, '');
select v #= hstore('c', '3.45') from testhstore1 v;
select v #= hstore('d', '3.45') from testhstore1 v;
select v #= hstore('e', '123') from testhstore1 v;
select v #= hstore('c', null) from testhstore1 v;
select v #= hstore('e', null) from testhstore0 v;
select v #= hstore('e', null) from testhstore1 v;
select v #= (hstore('b', 'foo') || hstore('a', '123')) from testhstore1 v;
select v #= (hstore('b', 'foo') || hstore('e', '123')) from testhstore1 v;
select v #= hstore '' from testhstore0 v;
select v #= hstore '' from testhstore1 v;
select null::testhstore1 #= (hstore('c', '3.45') || hstore('a', '123'));
select null::testhstore1 #= (hstore('c', '3.45') || hstore('e', '123'));
select null::testhstore0 #= hstore '';
select null::testhstore1 #= hstore '';
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;
*/
-- keys/values
select akeys(hs_concat('aa=>1 , b=>2, cq=>3'::hstore, 'cq=>l, b=>g, fg=>f'));
akeys
--------------
{b,aa,cq,fg}
(1 row)
select akeys('""=>1');
akeys
-------
{""}
(1 row)
select akeys('');
akeys
-------
(1 row)
select avals(hs_concat('aa=>1 , b=>2, cq=>3'::hstore, 'cq=>l, b=>g, fg=>f'));
avals
-----------
{g,1,l,f}
(1 row)
select avals(hs_concat('aa=>1 , b=>2, cq=>3'::hstore, 'cq=>l, b=>g, fg=>NULL'));
avals
--------------
{g,1,l,NULL}
(1 row)
select avals('""=>1');
avals
-------
{1}
(1 row)
select avals('');
avals
-------
(1 row)
select hstore_to_array('aa=>1, cq=>l, b=>g, fg=>NULL'::hstore);
hstore_to_array
-------------------------
{b,g,aa,1,cq,l,fg,NULL}
(1 row)
--select %% 'aa=>1, cq=>l, b=>g, fg=>NULL';
select hstore_to_matrix('aa=>1, cq=>l, b=>g, fg=>NULL'::hstore);
hstore_to_matrix
---------------------------------
{{b,g},{aa,1},{cq,l},{fg,NULL}}
(1 row)
--select %# 'aa=>1, cq=>l, b=>g, fg=>NULL';
select * from skeys(hs_concat('aa=>1 , b=>2, cq=>3'::hstore, 'cq=>l, b=>g, fg=>f'));
skeys
-------
b
aa
cq
fg
(4 rows)
select * from skeys('""=>1');
skeys
-------
(1 row)
select * from skeys('');
skeys
-------
(0 rows)
select * from svals(hs_concat('aa=>1 , b=>2, cq=>3'::hstore, 'cq=>l, b=>g, fg=>f'));
svals
-------
g
1
l
f
(4 rows)
select *, svals is null from svals(hs_concat('aa=>1 , b=>2, cq=>3'::hstore, 'cq=>l, b=>g, fg=>NULL'));
svals | ?column?
-------+----------
g | f
1 | f
l | f
| t
(4 rows)
select * from svals('""=>1');
svals
-------
1
(1 row)
select * from svals('');
svals
-------
(0 rows)
select * from each('aaa=>bq, b=>NULL, ""=>1 ');
key | value
-----+-------
| 1
b |
aaa | bq
(3 rows)
-- hs_contains
select hs_contains('a=>b, b=>1, c=>NULL'::hstore, 'a=>b'::hstore);
hs_contains
-------------
t
(1 row)
select hs_contains('a=>b, b=>1, c=>NULL'::hstore, 'a=>b, c=>NULL'::hstore);
hs_contains
-------------
t
(1 row)
select hs_contains('a=>b, b=>1, c=>NULL'::hstore, 'a=>b, g=>NULL'::hstore);
hs_contains
-------------
f
(1 row)
select hs_contains('a=>b, b=>1, c=>NULL'::hstore, 'g=>NULL'::hstore);
hs_contains
-------------
f
(1 row)
select hs_contains('a=>b, b=>1, c=>NULL'::hstore, 'a=>c'::hstore);
hs_contains
-------------
f
(1 row)
select hs_contains('a=>b, b=>1, c=>NULL'::hstore, 'a=>b'::hstore);
hs_contains
-------------
t
(1 row)
select hs_contains('a=>b, b=>1, c=>NULL'::hstore, 'a=>b, c=>q'::hstore);
hs_contains
-------------
f
(1 row)
CREATE TABLE testhstore (h hstore);
\copy testhstore from 'data/hstore.data'
select count(*) from testhstore where hs_contains(h, 'wait=>NULL'::hstore);
count
-------
1
(1 row)
select count(*) from testhstore where hs_contains(h, 'wait=>CC'::hstore);
count
-------
15
(1 row)
select count(*) from testhstore where hs_contains(h, 'wait=>CC, public=>t'::hstore);
count
-------
2
(1 row)
select count(*) from testhstore where exist(h, 'public');
count
-------
194
(1 row)
select count(*) from testhstore where exists_any(h, ARRAY['public','disabled']);
count
-------
337
(1 row)
select count(*) from testhstore where exists_all(h, ARRAY['public','disabled']);
count
-------
42
(1 row)
/*
create index hidx on testhstore using gist(h);
set enable_seqscan=off;
select count(*) from testhstore where h @> 'wait=>NULL';
select count(*) from testhstore where h @> 'wait=>CC';
select count(*) from testhstore where h @> 'wait=>CC, public=>t';
select count(*) from testhstore where h ? 'public';
select count(*) from testhstore where h ?| ARRAY['public','disabled'];
select count(*) from testhstore where h ?& ARRAY['public','disabled'];
drop index hidx;
create index hidx on testhstore using gin (h);
set enable_seqscan=off;
select count(*) from testhstore where h @> 'wait=>NULL';
select count(*) from testhstore where h @> 'wait=>CC';
select count(*) from testhstore where h @> 'wait=>CC, public=>t';
select count(*) from testhstore where h ? 'public';
select count(*) from testhstore where h ?| ARRAY['public','disabled'];
select count(*) from testhstore where h ?& ARRAY['public','disabled'];
select count(*) from (select (each(h)).key from testhstore) as wow ;
select key, count(*) from (select (each(h)).key from testhstore) as wow group by key order by count desc, key;
-- sort/hash
select count(distinct h) from testhstore;
set enable_hashagg = false;
select count(*) from (select h from (select * from testhstore union all select * from testhstore) hs group by h) hs2;
set enable_hashagg = true;
set enable_sort = false;
select count(*) from (select h from (select * from testhstore union all select * from testhstore) hs group by h) hs2;
select distinct * from (values (hs_concat(hstore '', '')),('')) v(h);
set enable_sort = true;
-- btree
drop index hidx;
create index hidx on testhstore using btree (h);
set enable_seqscan=off;
select count(*) from testhstore where h #># 'p=>1';
select count(*) from testhstore where h = 'pos=>98, line=>371, node=>CBA, indexed=>t';
*/
Reference in New Issue View Git Blame Copy Permalink