9. array 和 hstore 類型 · postgresql教程

# 9. array 和 hstore 類型 PostgreSQL支持最豐富的數據類型，更是最具有Nosql的特性。本節的內容會基于官方的[active\_record\_postgresql](https://github.com/rails/rails/blob/master/guides/source/active_record_postgresql.md)，進行擴展和完善。 #### 1. 二進制類型 PostgreSQL可以直接存儲二進制的文件。例如圖片、文檔，視頻等。 ``` rails g model document payload:binary # db/migrate/20140207133952_create_documents.rb class CreateDocuments < ActiveRecord::Migration def change create_table :documents do |t| t.binary :payload t.timestamps null: false end end end # Usage data = File.read(Rails.root + "tmp/output.pdf") Document.create payload: data ``` #### 2. 數組其他數據庫系統也是可以存數組的，不過還是最終以字符串的形式存的，取出和讀取都是用程序來序列化。假如不用字符串存，那就得多準備一張表，例如，一篇文章要記錄什么人收藏過。就得多一張表，每次判斷用戶是否收藏過，就得查那張表，而數據以冗余的方式存在數據中，就是把user\_id存進去一個字段，這樣就大大方便了。**PostgreSQL**默認就支持數據的存取，還支持對數據的各種操作，比如查找等。 ``` # db/migrate/20140207133952_create_books.rb create_table :books do |t| t.string 'title' t.string 'tags', array: true t.integer 'ratings', array: true end add_index :books, :tags, using: 'gin' add_index :books, :ratings, using: 'gin' # app/models/book.rb class Book < ActiveRecord::Base end # Usage Book.create title: "Brave New World", tags: ["fantasy", "fiction"], ratings: [4, 5] ## Books for a single tag Book.where("'fantasy' = ANY (tags)") ## Books for multiple tags Book.where("tags @> ARRAY[?]::varchar[]", ["fantasy", "fiction"]) ## Books with 3 or more ratings Book.where("array_length(ratings, 1) >= 3") ``` **PostgreSQL**還支持對array的各種操作，[官方文檔](http://www.postgresql.org/docs/9.4/static/arrays.html)給了詳細的解釋。 ``` # 返回數組第一個元素和第二個元素不相同的記錄 Book.where("ratings[0] <> ratings[1]") # 查找第一個tag Book.select("title, tags[0] as tag") # 返回數組的維數 Book.select("title, array_dims(tags)") ``` 像類似array\_dims的操作符，官方這篇文章[functions-array](http://www.postgresql.org/docs/9.4/static/functions-array.html)有詳細的記錄。比如，把數組進行類似join的操作。 ``` Book.select("title, array_to_string(tags, '_')") SELECT title, array_to_string(tags, '_') FROM "books"; title | array_to_string -----------------+----------------- Brave New World | fantasy_fiction (1 row) ``` #### 3. Hstore Hstore是PostgreSQL的一個擴展，它能夠存放鍵值對，比如，json，hash等半結構化數據。一般的數據庫系統是沒有這種功能，而這種需求是很常見的，所以說，PostgreSQL是最具Nosql特性的。只要前端通過js提交一些hash或json，或者通過form提交一些數據，就能直接以json等形式存到數據庫中。例如，一個用戶有1個，0個，或多個聯系人，如果以關系型數據庫來存的話，只能多建立一張表來存，然后用has\_many，belongs\_to來處理。而Hstore就是以字段的形式來存，這就很方便了。 ``` # 開啟擴展 rails365_dev=# CREATE EXTENSION hstore; # 或者 class AddHstore < ActiveRecord::Migration def up execute 'CREATE EXTENSION IF NOT EXISTS hstore' end def down execute 'DROP EXTENSION hstore' end end # 或者 class AddHstore < ActiveRecord::Migration def change enable_extension 'hstore' end end rails g model profile settings:hstore # Usage Profile.create(settings: { "color" => "blue", "resolution" => "800x600" }) profile = Profile.first profile.settings # => {"color"=>"blue", "resolution"=>"800x600"} profile.settings = {"color" => "yellow", "resolution" => "1280x1024"} profile.save! ``` 像array一樣，Hstore也是支持很多操作的，官方文檔[hstore](http://www.postgresql.org/docs/current/static/hstore.html)給了詳細的描述。比如： ``` rails365_dev=# SELECT "profiles".settings -> 'color' FROM "profiles" ; ?column? ---------- yellow blue (2 rows) rails365_dev=# SELECT "profiles".settings ? 'color' FROM "profiles" ; ?column? ---------- t t (2 rows) rails365_dev=# SELECT hstore_to_json("profiles".settings) FROM "profiles" ; hstore_to_json --------------------------------------------------------------- {"color": "yellow", "resolution": "1280x1024"} {"color": "blue", "resolution": "[\"800x600\", \"750x670\"]"} (2 rows) rails365_dev=# SELECT "profiles".settings -> 'color' FROM "profiles" where settings->'color' = 'yellow'; ?column? ---------- yellow (1 row) ``` 更多詳細只要查看官文文檔就好了。關于Hstore有一個gem是[activerecord-postgres-hstore](https://github.com/diogob/activerecord-postgres-hstore)，這個gem提供了很多關于Hstore的查詢方法。 [using-postgres-hstore-rails4](http://jes.al/2013/11/using-postgres-hstore-rails4/)這篇文章介紹了Hstore的用法。其他的特性， “JSON”、"Range Types"、“Enumerated Types”、“UUID”等就不再贅述，要使用時，結合官方文檔查看即可。完結。