Hstore



在PostgreSQL 9.2版本中,查詢結果可以轉換為JSON數據類型後返回。通過新的PL/v8 Javascript腳本和PL/Coffee數據庫編程擴展,再加上可選的HStore 鍵值對存儲,用戶可以將PostgreSQL當作一個「NoSQL」的文檔數據庫來使用,同時還保留PostgreSQL的可靠性、靈活性和性能優勢。








本文測試環境在 Mac OS 下,Pg採用源碼編譯。, http://www.udpwork.com/item/3371.html

編譯 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)
Comments