PostgreSQL 的 array 和 hstore 类型 (九)

随风发布于2015-10-042772 次阅读

PostgreSQL 支持最丰富的数据类型，更是最具有 Nosql 的特性。本节的内容会基于官方的active_record_postgresql，进行扩展和完善。

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 的各种操作，官方文档给了详细的解释。

# 返回数组第一个元素和第二个元素不相同的记录
Book.where("ratings[0] <> ratings[1]")

# 查找第一个tag
Book.select("title, tags[0] as tag")

# 返回数组的维数
Book.select("title, array_dims(tags)")

像类似 array_dims 的操作符，官方这篇文章functions-array有详细的记录。

比如，把数组进行类似 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给了详细的描述。

比如：

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)