PostgreSQL的hstore初步学习

佘小宝的爹

　　安装hstore:
　　进入源代码的 /contrib/hstore 目录，然后执行gmake 和 gmake install:



[iyunv@pg200 hstore]# gmake
gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fpic -I. -I. -I../../src/include -D_GNU_SOURCE   -c -o hstore_io.o hstore_io.c
gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fpic -I. -I. -I../../src/include -D_GNU_SOURCE   -c -o hstore_op.o hstore_op.c
gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fpic -I. -I. -I../../src/include -D_GNU_SOURCE   -c -o hstore_gist.o hstore_gist.c
gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fpic -I. -I. -I../../src/include -D_GNU_SOURCE   -c -o hstore_gin.o hstore_gin.c
gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fpic -I. -I. -I../../src/include -D_GNU_SOURCE   -c -o hstore_compat.o hstore_compat.c
gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fpic -I. -I. -I../../src/include -D_GNU_SOURCE   -c -o crc32.o crc32.c
gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fpic -shared -o hstore.so hstore_io.o hstore_op.o hstore_gist.o hstore_gin.o hstore_compat.o crc32.o -L../../src/port  -Wl,-rpath,'/usr/local/pgsql/lib',--enable-new-dtags
　　gmake:



[iyunv@pg200 hstore]# gmake install
/bin/mkdir -p '/usr/local/pgsql/lib'
/bin/mkdir -p '/usr/local/pgsql/share/extension'
/bin/mkdir -p '/usr/local/pgsql/share/extension'
/bin/sh ../../config/install-sh -c -m 755  hstore.so '/usr/local/pgsql/lib/hstore.so'
/bin/sh ../../config/install-sh -c -m 644 ./hstore.control '/usr/local/pgsql/share/extension/'
/bin/sh ../../config/install-sh -c -m 644 ./hstore--1.1.sql ./hstore--1.0--1.1.sql ./hstore--unpackaged--1.0.sql  '/usr/local/pgsql/share/extension/'
[iyunv@pg200 hstore]#
　　然后，启动PostgreSQL，再启动psql后，安装hstore扩展：



postgres=# create extension hstore;
CREATE EXTENSION
postgres=#
　　进行测试：
　　建表：



postgres=# create table hstore_test(item_id serial, data hstore);
NOTICE:  CREATE TABLE will create implicit sequence "hstore_test_item_id_seq" for serial column "hstore_test.item_id"
CREATE TABLE
postgres=#
　　插入数据：



postgres=# INSERT INTO hstore_test (data) VALUES ('"key1"=>"value1", "key2"=>"value2", "key3"=>"value3"');
INSERT 0 1
postgres=# select * from hstore_test;
item_id |                         data                        
---------+------------------------------------------------------
1 | "key1"=>"value1", "key2"=>"value2", "key3"=>"value3"
(1 row)
postgres=#
　　修改数据：



postgres=# UPDATE hstore_test SET data = delete(data, 'key2')
postgres-# ;
UPDATE 1
postgres=# select * from hstore_test;
item_id |                data               
---------+------------------------------------
1 | "key1"=>"value1", "key3"=>"value3"
(1 row)
postgres=#



postgres=# UPDATE hstore_test SET data = data || '"key4"=>"some value"'::hstore;
UPDATE 1
postgres=# select * from hstore_test;
item_id |                           data                           
---------+----------------------------------------------------------
1 | "key1"=>"value1", "key3"=>"value3", "key4"=>"some value"
(1 row)
postgres=#
　　按Key值查询：



postgres=# SELECT * FROM hstore_test WHERE data ? 'key4';
item_id |                           data                           
---------+----------------------------------------------------------
1 | "key1"=>"value1", "key3"=>"value3", "key4"=>"some value"
(1 row)
postgres=#
postgres=# SELECT * FROM hstore_test WHERE NOT data ? 'key5';
item_id |                           data                           
---------+----------------------------------------------------------
1 | "key1"=>"value1", "key3"=>"value3", "key4"=>"some value"
(1 row)
postgres=# SELECT * FROM hstore_test WHERE data @> '"key4"=>"some value"'::hstore;
item_id |                           data                           
---------+----------------------------------------------------------
1 | "key1"=>"value1", "key3"=>"value3", "key4"=>"some value"
(1 row)
postgres=# SELECT data -> 'key4' FROM hstore_test;
?column?  
------------
some value
(1 row)
postgres=# SELECT item_id, (each(data)).* FROM hstore_test WHERE item_id = 2;
item_id | key | value
---------+-----+-------
(0 rows)
postgres=# SELECT item_id, (each(data)).* FROM hstore_test WHERE item_id = 1;
item_id | key  |   value   
---------+------+------------
1 | key1 | value1
1 | key3 | value3
1 | key4 | some value
(3 rows)
postgres=#