安装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 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/' [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=#
