<ruby id="bdb3f"></ruby>

    <p id="bdb3f"><cite id="bdb3f"></cite></p>

      <p id="bdb3f"><cite id="bdb3f"><th id="bdb3f"></th></cite></p><p id="bdb3f"></p>
        <p id="bdb3f"><cite id="bdb3f"></cite></p>

          <pre id="bdb3f"></pre>
          <pre id="bdb3f"><del id="bdb3f"><thead id="bdb3f"></thead></del></pre>

          <ruby id="bdb3f"><mark id="bdb3f"></mark></ruby><ruby id="bdb3f"></ruby>
          <pre id="bdb3f"><pre id="bdb3f"><mark id="bdb3f"></mark></pre></pre><output id="bdb3f"></output><p id="bdb3f"></p><p id="bdb3f"></p>

          <pre id="bdb3f"><del id="bdb3f"><progress id="bdb3f"></progress></del></pre>

                <ruby id="bdb3f"></ruby>

                合規國際互聯網加速 OSASE為企業客戶提供高速穩定SD-WAN國際加速解決方案。 廣告
                # 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”等就不再贅述,要使用時,結合官方文檔查看即可。 完結。
                  <ruby id="bdb3f"></ruby>

                  <p id="bdb3f"><cite id="bdb3f"></cite></p>

                    <p id="bdb3f"><cite id="bdb3f"><th id="bdb3f"></th></cite></p><p id="bdb3f"></p>
                      <p id="bdb3f"><cite id="bdb3f"></cite></p>

                        <pre id="bdb3f"></pre>
                        <pre id="bdb3f"><del id="bdb3f"><thead id="bdb3f"></thead></del></pre>

                        <ruby id="bdb3f"><mark id="bdb3f"></mark></ruby><ruby id="bdb3f"></ruby>
                        <pre id="bdb3f"><pre id="bdb3f"><mark id="bdb3f"></mark></pre></pre><output id="bdb3f"></output><p id="bdb3f"></p><p id="bdb3f"></p>

                        <pre id="bdb3f"><del id="bdb3f"><progress id="bdb3f"></progress></del></pre>

                              <ruby id="bdb3f"></ruby>

                              哎呀哎呀视频在线观看