Postgre支持的數(shù)據(jù)類型非常豐富, 可以儲(chǔ)存 mac地址 ip地址 path路徑 數(shù)組 等類型.
大家可能用到的情況不是很多, 不過有種數(shù)據(jù)結(jié)構(gòu)你可能會(huì)需要 hash類型

datatype.png

hstore 儲(chǔ)存的結(jié)構(gòu)類似于MongoDB, 想體驗(yàn)nosql的便利, 可以用用hstore啊

開啟 hstore extension

CREATE EXTENSION hstore;

表結(jié)構(gòu)

CREATE TABLE books (
 id serial primary key,
 title VARCHAR (255),
 attr hstore
);

插入數(shù)據(jù)

INSERT INTO books (title, attr)
VALUES
 (
 'PostgreSQL Tutorial',

  '
    "paperback" => "243",
    "publisher" => "postgresqltutorial.com",
    "language"  => "English",
    "ISBN-13"   => "978-1449370000",
    "weight"    => "11.2 ounces"
  '
);

------

-- "author" => "shooter" 多了個(gè)作者

INSERT INTO books (title, attr)
VALUES
(
 'PostgreSQL Cheat Sheet',
 '
  "author" => "shooter",
  "paperback" => "5",
  "publisher" => "postgresqltutorial.com",
  "language"  => "English",
  "ISBN-13"   => "978-1449370001",
  "weight"    => "1 ounces"
 '
);

查詢

SELECT attr FROM books; -- 普通查詢

SELECT
 attr -> 'weight' AS weight
FROM
 books
WHERE
 attr -> 'ISBN-13' = '978-1449370000';

SELECT
 title
FROM
 books
WHERE
 attr @> '"weight"=>"11.2 ounces"' :: hstore;

更新

UPDATE books
SET attr = attr || '"freeshipping"=>"yes"' :: hstore;

UPDATE books
SET   attr = '"author"=>"shooter"' :: hstore;

刪除屬性

UPDATE books 
SET attr = delete(attr, 'freeshipping');

hstore 跟json/jsonb的 性能差異，在用了后在說吧

參考:
Use unstructured datatypes Hstore vs JSON vs JSONB

http://chenxiaoyu.org/2013/11/28/postgresql-array/
http://www.postgresqltutorial.com/postgresql-hstore/
https://www.zhihu.com/question/20010554/answer/62628256

https://my.oschina.net/swuly302/blog/143746 2013年翻譯的文章 只供參考