安装hstore:
进入源代码的 /contrib/hstore 目录，然后执行gmake 和 gmake install:
[root@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:
[root@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 hstore.so '/usr/local/pgsql/lib/hstore.so'
/bin/sh ../../config/install-sh -c -m ./hstore.control '/usr/local/pgsql/share/extension/'
/bin/sh ../../config/install-sh -c -m ./hstore--1.1.sql ./hstore--1.0--1.1.sql ./hstore--unpackaged--1.0.sql '/usr/local/pgsql/share/extension/'
[root@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=#