smallfish weblog

原文地址：http://psql.info/?p=41

现在满地都是KV数据库的文字，PostgreSQL 也有类似的结构。不过是通过其强大的扩展方式实现的。

官网文档请参考：http://www.postgresql.org/docs/current/static/hstore.html

本文测试环境在 Mac OS 下，Pg采用源码编译。

编译 hstore 扩展

mac:~ smallfish$ cd Downloads/postgresql-9.0.1/contrib/hstore/
mac:hstore smallfish$ make
... 一堆编译信息
mac:hstore smallfish$ sudo make install

导入到数据库中，注意必须以 postgres 用户，如果需要装入到指定数据库，请指明。这里采用默认数据库。

mac:hstore smallfish$ ls /opt/postgresql/share/contrib/
hstore.sql              uninstall_hstore.sql
mac:hstore smallfish$ psql -U postgres -f /opt/postgresql/share/contrib/hstore.sql
... 一堆导入命令

进入数据库，建一个测试表

postgres=# CREATE TABLE testhstore (id SERIAL, value hstore);
NOTICE:  CREATE TABLE will create implicit sequence "testhstore_id_seq" for serial column "testhstore.id"
CREATE TABLE

查看下表结构

postgres=# \d
                List of relations
 Schema |       Name        |   Type   |  Owner
--------+-------------------+----------+----------
 public | testhstore        | table    | postgres
 public | testhstore_id_seq | sequence | postgres
(2 rows)

postgres=# \d testhstore;
                         Table "public.testhstore"
 Column |  Type   |                        Modifiers
--------+---------+---------------------------------------------------------
 id     | integer | not null default nextval('testhstore_id_seq'::regclass)
 value  | hstore  |

尝试下简单 hstore类型

postgres=# select 'a=>1, b=>2'::hstore;
       hstore
--------------------
 "a"=>"1", "b"=>"2"
(1 row)

postgres=# select 'a=>1, b=>a'::hstore;
       hstore
--------------------
 "a"=>"1", "b"=>"a"
(1 row)

写几条测试数据先

postgres=# INSERT INTO testhstore (value) VALUES ('name=>smallfish, age=>29'::hstore);
INSERT 0 1
postgres=# SELECT * FROM testhstore;
 id |              value
----+----------------------------------
  1 | "age"=>"29", "name"=>"smallfish"
(1 row)

postgres=# INSERT INTO testhstore (value) VALUES ('name=>nnfish, age=>20'::hstore);
INSERT 0 1
postgres=# INSERT INTO testhstore (value) VALUES ('name=>aaa, age=>30, addr=>China'::hstore);
INSERT 0 1

查看下所有数据

postgres=# SELECT * FROM testhstore;
 id |                    value
----+---------------------------------------------
  1 | "age"=>"29", "name"=>"smallfish"
  2 | "age"=>"20", "name"=>"nnfish"
  3 | "age"=>"30", "addr"=>"China", "name"=>"aaa"
(3 rows)

查询列里面的指定 key

postgres=# SELECT id, value->'name' AS name FROM testhstore;
 id |   name
----+-----------
  1 | smallfish
  2 | nnfish
  3 | aaa
(3 rows)

postgres=# SELECT id, value->'name', value->'age' AS age FROM testhstore;
 id | ?column?  | age
----+-----------+-----
  1 | smallfish | 29
  2 | nnfish    | 20
  3 | aaa       | 30
(3 rows)

修改列某 key 值

postgres=# UPDATE testhstore SET value=value||('addr=>Shanghai') WHERE id = 2;
UPDATE 1

postgres=# SELECT * FROM testhstore;
 id |                       value
----+---------------------------------------------------
  1 | "age"=>"29", "name"=>"smallfish"
  3 | "age"=>"30", "addr"=>"China", "name"=>"aaa"
  2 | "age"=>"20", "addr"=>"Shanghai", "name"=>"nnfish"
(3 rows)

删除列里某 key

postgres=# UPDATE testhstore SET value=delete(value, 'addr') WHERE id = 3;
UPDATE 1

postgres=# SELECT * FROM testhstore;
 id |                       value
----+---------------------------------------------------
  1 | "age"=>"29", "name"=>"smallfish"
  2 | "age"=>"20", "addr"=>"Shanghai", "name"=>"nnfish"
  3 | "age"=>"30", "name"=>"aaa"
(3 rows)

按条件查询列里某 key ，注意要数据类型，CAST 强转。默认都是字符串的值。

postgres=# SELECT * FROM testhstore WHERE (value->'age')::INT > 20;
 id |              value
----+----------------------------------
  1 | "age"=>"29", "name"=>"smallfish"
  3 | "age"=>"30", "name"=>"aaa"
(2 rows)

postgres=# SELECT * FROM testhstore WHERE value->'name' = 'smallfish';
 id |              value
----+----------------------------------
  1 | "age"=>"29", "name"=>"smallfish"
(1 row)