更多資訊請參考 rubyonrails.org:

Active Record 與 PostgreSQL

本指南涵蓋 Active Record 的 PostgreSQL 特定用法。

閱讀本指南後，您將了解

  • 如何使用 PostgreSQL 的資料類型。
  • 如何使用 UUID 主鍵。
  • 如何在索引中包含非索引鍵欄位。
  • 如何使用可延遲的外鍵。
  • 如何使用唯一約束。
  • 如何實作排除約束。
  • 如何使用 PostgreSQL 實作全文檢索。
  • 如何使用資料庫視圖來支援您的 Active Record 模型。

為了使用 PostgreSQL 配接器，您需要安裝至少 9.3 版。不支援舊版本。

若要開始使用 PostgreSQL，請查看設定 Rails 指南。其中描述如何正確設定 Active Record 以使用 PostgreSQL。

1 資料類型

PostgreSQL 提供許多特定的資料類型。以下是 PostgreSQL 配接器支援的類型清單。

1.1 Bytea

# db/migrate/20140207133952_create_documents.rb
create_table :documents do |t|
  t.binary "payload"
end

# app/models/document.rb
class Document < ApplicationRecord
end

# Usage
data = File.read(Rails.root + "tmp/output.pdf")
Document.create payload: data

1.2 陣列

# 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 < ApplicationRecord
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")

1.3 Hstore

您需要啟用 hstore 擴展才能使用 hstore。

# db/migrate/20131009135255_create_profiles.rb
class CreateProfiles < ActiveRecord::Migration[8.0]
  enable_extension "hstore" unless extension_enabled?("hstore")
  create_table :profiles do |t|
    t.hstore "settings"
  end
end

# app/models/profile.rb
class Profile < ApplicationRecord
end

irb> Profile.create(settings: { "color" => "blue", "resolution" => "800x600" })

irb> profile = Profile.first
irb> profile.settings
=> {"color"=>"blue", "resolution"=>"800x600"}

irb> profile.settings = {"color" => "yellow", "resolution" => "1280x1024"}
irb> profile.save!

irb> Profile.where("settings->'color' = ?", "yellow")
=> #<ActiveRecord::Relation [#<Profile id: 1, settings: {"color"=>"yellow", "resolution"=>"1280x1024"}>]>

1.4 JSON 和 JSONB

# db/migrate/20131220144913_create_events.rb
# ... for json datatype:
create_table :events do |t|
  t.json "payload"
end
# ... or for jsonb datatype:
create_table :events do |t|
  t.jsonb "payload"
end

# app/models/event.rb
class Event < ApplicationRecord
end

irb> Event.create(payload: { kind: "user_renamed", change: ["jack", "john"]})

irb> event = Event.first
irb> event.payload
=> {"kind"=>"user_renamed", "change"=>["jack", "john"]}

## Query based on JSON document
# The -> operator returns the original JSON type (which might be an object), whereas ->> returns text
irb> Event.where("payload->>'kind' = ?", "user_renamed")

1.5 範圍類型

此類型對應至 Ruby 的 Range 物件。

# db/migrate/20130923065404_create_events.rb
create_table :events do |t|
  t.daterange "duration"
end

# app/models/event.rb
class Event < ApplicationRecord
end

irb> Event.create(duration: Date.new(2014, 2, 11)..Date.new(2014, 2, 12))

irb> event = Event.first
irb> event.duration
=> Tue, 11 Feb 2014...Thu, 13 Feb 2014

## All Events on a given date
irb> Event.where("duration @> ?::date", Date.new(2014, 2, 12))

## Working with range bounds
irb> event = Event.select("lower(duration) AS starts_at").select("upper(duration) AS ends_at").first

irb> event.starts_at
=> Tue, 11 Feb 2014
irb> event.ends_at
=> Thu, 13 Feb 2014

1.6 複合類型

目前沒有對複合類型的特殊支援。它們會對應至一般的文字欄位

CREATE TYPE full_address AS
(
  city VARCHAR(90),
  street VARCHAR(90)
);

# db/migrate/20140207133952_create_contacts.rb
execute <<-SQL
  CREATE TYPE full_address AS
  (
    city VARCHAR(90),
    street VARCHAR(90)
  );
SQL
create_table :contacts do |t|
  t.column :address, :full_address
end

# app/models/contact.rb
class Contact < ApplicationRecord
end

irb> Contact.create address: "(Paris,Champs-Élysées)"
irb> contact = Contact.first
irb> contact.address
=> "(Paris,Champs-Élysées)"
irb> contact.address = "(Paris,Rue Basse)"
irb> contact.save!

1.7 列舉類型

此類型可以對應為一般的文字欄位，或對應至 ActiveRecord::Enum。

# db/migrate/20131220144913_create_articles.rb
def change
  create_enum :article_status, ["draft", "published", "archived"]

  create_table :articles do |t|
    t.enum :status, enum_type: :article_status, default: "draft", null: false
  end
end

您也可以建立列舉類型，並將列舉欄位新增至現有的表格

# db/migrate/20230113024409_add_status_to_articles.rb
def change
  create_enum :article_status, ["draft", "published", "archived"]

  add_column :articles, :status, :enum, enum_type: :article_status, default: "draft", null: false
end

以上的遷移都是可還原的，但如果需要，您可以定義個別的 #up 和 #down 方法。請確保在刪除列舉類型之前，移除任何依賴該列舉類型的欄位或表格

def down
  drop_table :articles

  # OR: remove_column :articles, :status
  drop_enum :article_status
end

在模型中宣告列舉屬性會新增輔助方法，並防止將無效值指派給該類別的實例

# app/models/article.rb
class Article < ApplicationRecord
  enum :status, {
    draft: "draft", published: "published", archived: "archived"
  }, prefix: true
end

irb> article = Article.create
irb> article.status
=> "draft" # default status from PostgreSQL, as defined in migration above

irb> article.status_published!
irb> article.status
=> "published"

irb> article.status_archived?
=> false

irb> article.status = "deleted"
ArgumentError: 'deleted' is not a valid status

若要重新命名列舉，您可以使用 rename_enum 並更新任何模型的使用方式

# db/migrate/20150718144917_rename_article_status.rb
def change
  rename_enum :article_status, :article_state
end

若要新增值，您可以使用 add_enum_value

# db/migrate/20150720144913_add_new_state_to_articles.rb
def up
  add_enum_value :article_state, "archived" # will be at the end after published
  add_enum_value :article_state, "in review", before: "published"
  add_enum_value :article_state, "approved", after: "in review"
  add_enum_value :article_state, "rejected", if_not_exists: true # won't raise an error if the value already exists
end

列舉值無法刪除，這也表示 add_enum_value 是不可還原的。您可以在這裡閱讀原因。

若要重新命名值，您可以使用 rename_enum_value

# db/migrate/20150722144915_rename_article_state.rb
def change
  rename_enum_value :article_state, from: "archived", to: "deleted"
end

提示：若要顯示您擁有的所有列舉的所有值，您可以在 bin/rails db 或 psql 主控台中呼叫此查詢

SELECT n.nspname AS enum_schema,
       t.typname AS enum_name,
       e.enumlabel AS enum_value
  FROM pg_type t
      JOIN pg_enum e ON t.oid = e.enumtypid
      JOIN pg_catalog.pg_namespace n ON n.oid = t.typnamespace

1.8 UUID

如果您使用的 PostgreSQL 版本早於 13.0，您可能需要啟用特殊的擴展才能使用 UUID。啟用 pgcrypto 擴展 (PostgreSQL >= 9.4) 或 uuid-ossp 擴展 (適用於更早的版本)。

# db/migrate/20131220144913_create_revisions.rb
create_table :revisions do |t|
  t.uuid :identifier
end

# app/models/revision.rb
class Revision < ApplicationRecord
end

irb> Revision.create identifier: "A0EEBC99-9C0B-4EF8-BB6D-6BB9BD380A11"

irb> revision = Revision.first
irb> revision.identifier
=> "a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11"

您可以使用 uuid 類型在遷移中定義參考

# db/migrate/20150418012400_create_blog.rb
enable_extension "pgcrypto" unless extension_enabled?("pgcrypto")
create_table :posts, id: :uuid

create_table :comments, id: :uuid do |t|
  # t.belongs_to :post, type: :uuid
  t.references :post, type: :uuid
end

# app/models/post.rb
class Post < ApplicationRecord
  has_many :comments
end

# app/models/comment.rb
class Comment < ApplicationRecord
  belongs_to :post
end

有關將 UUID 用作主鍵的更多詳細資訊，請參閱本節。

1.9 位元字串類型

# db/migrate/20131220144913_create_users.rb
create_table :users, force: true do |t|
  t.column :settings, "bit(8)"
end

# app/models/user.rb
class User < ApplicationRecord
end

irb> User.create settings: "01010011"
irb> user = User.first
irb> user.settings
=> "01010011"
irb> user.settings = "0xAF"
irb> user.settings
=> "10101111"
irb> user.save!

1.10 網路位址類型

類型 inet 和 cidr 對應至 Ruby 的 IPAddr 物件。macaddr 類型對應至一般的文字。

# db/migrate/20140508144913_create_devices.rb
create_table(:devices, force: true) do |t|
  t.inet "ip"
  t.cidr "network"
  t.macaddr "address"
end

# app/models/device.rb
class Device < ApplicationRecord
end

irb> macbook = Device.create(ip: "192.168.1.12", network: "192.168.2.0/24", address: "32:01:16:6d:05:ef")

irb> macbook.ip
=> #<IPAddr: IPv4:192.168.1.12/255.255.255.255>

irb> macbook.network
=> #<IPAddr: IPv4:192.168.2.0/255.255.255.0>

irb> macbook.address
=> "32:01:16:6d:05:ef"

1.11 幾何類型

除 points 外，所有幾何類型都對應至一般的文字。點會轉換為包含 x 和 y 座標的陣列。

1.12 間隔

此類型對應至 ActiveSupport::Duration 物件。

# db/migrate/20200120000000_create_events.rb
create_table :events do |t|
  t.interval "duration"
end

# app/models/event.rb
class Event < ApplicationRecord
end

irb> Event.create(duration: 2.days)

irb> event = Event.first
irb> event.duration
=> 2 days

2 UUID 主鍵

您需要啟用 pgcrypto (僅適用於 PostgreSQL >= 9.4) 或 uuid-ossp 擴展，才能產生隨機 UUID。

# db/migrate/20131220144913_create_devices.rb
enable_extension "pgcrypto" unless extension_enabled?("pgcrypto")
create_table :devices, id: :uuid do |t|
  t.string :kind
end

# app/models/device.rb
class Device < ApplicationRecord
end

irb> device = Device.create
irb> device.id
=> "814865cd-5a1d-4771-9306-4268f188fe9e"

如果沒有將 :default 選項傳遞至 create_table，則會假設使用 gen_random_uuid() (來自 pgcrypto)。

若要將 Rails 模型產生器用於以 UUID 作為主鍵的表格，請將 --primary-key-type=uuid 傳遞至模型產生器。

例如

$ rails generate model Device --primary-key-type=uuid kind:string

當建立具有將參考此 UUID 的外鍵的模型時，請將 uuid 視為原生欄位類型，例如

$ rails generate model Case device_id:uuid

3 索引

PostgreSQL 包含各種索引選項。除了常見的索引選項之外，PostgreSQL 配接器還支援以下選項

3.1 Include

建立新索引時，可以使用 :include 選項包含非索引鍵欄位。這些索引鍵不會用於搜尋的索引掃描，但在僅限索引的掃描期間，可以在不必存取相關表格的情況下讀取。

# db/migrate/20131220144913_add_index_users_on_email_include_id.rb

add_index :users, :email, include: :id

支援多個欄位

# db/migrate/20131220144913_add_index_users_on_email_include_id_and_created_at.rb

add_index :users, :email, include: [:id, :created_at]

4 產生欄位

PostgreSQL 12.0 版開始支援產生欄位。

# db/migrate/20131220144913_create_users.rb
create_table :users do |t|
  t.string :name
  t.virtual :name_upcased, type: :string, as: "upper(name)", stored: true
end

# app/models/user.rb
class User < ApplicationRecord
end

# Usage
user = User.create(name: "John")
User.last.name_upcased # => "JOHN"

5 可延遲的外鍵

依預設，PostgreSQL 中的表格約束會在每個陳述式之後立即檢查。它刻意不允許建立參考表格中尚不存在參考記錄的記錄。透過將 DEFERRABLE 新增至外鍵定義，可以在交易提交時稍後執行此完整性檢查。若要依預設延遲所有檢查，可以設定為 DEFERRABLE INITIALLY DEFERRED。Rails 藉由將 :deferrable 索引鍵新增至 add_reference 和 add_foreign_key 方法中的 foreign_key 選項來公開此 PostgreSQL 功能。

其中一個範例是在交易中建立循環相依性，即使您已建立外鍵

add_reference :person, :alias, foreign_key: { deferrable: :deferred }
add_reference :alias, :person, foreign_key: { deferrable: :deferred }

如果參考是以 foreign_key: true 選項建立的，則在執行第一個 INSERT 陳述式時，以下交易會失敗。但如果設定 deferrable: :deferred 選項，則不會失敗。

ActiveRecord::Base.lease_connection.transaction do
  person = Person.create(id: SecureRandom.uuid, alias_id: SecureRandom.uuid, name: "John Doe")
  Alias.create(id: person.alias_id, person_id: person.id, name: "jaydee")
end

當 :deferrable 選項設定為 :immediate 時，讓外鍵保留立即檢查約束的預設行為，但允許在交易中使用 set_constraints 手動延遲檢查。這會導致在外鍵提交交易時進行檢查

ActiveRecord::Base.lease_connection.transaction do
  ActiveRecord::Base.lease_connection.set_constraints(:deferred)
  person = Person.create(alias_id: SecureRandom.uuid, name: "John Doe")
  Alias.create(id: person.alias_id, person_id: person.id, name: "jaydee")
end

依預設，:deferrable 為 false，且一律立即檢查約束。

6 唯一約束

# db/migrate/20230422225213_create_items.rb
create_table :items do |t|
  t.integer :position, null: false
  t.unique_constraint [:position], deferrable: :immediate
end

如果您想要將現有的唯一索引變更為可延遲，您可以使用 :using_index 來建立可延遲的唯一約束。

add_unique_constraint :items, deferrable: :deferred, using_index: "index_items_on_position"

與外鍵類似，可以透過將 :deferrable 設定為 :immediate 或 :deferred 來延遲唯一約束。依預設，:deferrable 為 false，且一律立即檢查約束。

7 排除約束

# db/migrate/20131220144913_create_products.rb
create_table :products do |t|
  t.integer :price, null: false
  t.daterange :availability_range, null: false

  t.exclusion_constraint "price WITH =, availability_range WITH &&", using: :gist, name: "price_check"
end

與外鍵類似，排除約束可以透過將 :deferrable 設定為 :immediate 或 :deferred 來延遲檢查。預設情況下，:deferrable 為 false，並且約束總是會立即檢查。

8 全文檢索

# db/migrate/20131220144913_create_documents.rb
create_table :documents do |t|
  t.string :title
  t.string :body
end

add_index :documents, "to_tsvector('english', title || ' ' || body)", using: :gin, name: "documents_idx"

# app/models/document.rb
class Document < ApplicationRecord
end

# Usage
Document.create(title: "Cats and Dogs", body: "are nice!")

## all documents matching 'cat & dog'
Document.where("to_tsvector('english', title || ' ' || body) @@ to_tsquery(?)",
                 "cat & dog")

您可以選擇將向量儲存為自動產生的欄位（PostgreSQL 12.0 起支援）。

# db/migrate/20131220144913_create_documents.rb
create_table :documents do |t|
  t.string :title
  t.string :body

  t.virtual :textsearchable_index_col,
            type: :tsvector, as: "to_tsvector('english', title || ' ' || body)", stored: true
end

add_index :documents, :textsearchable_index_col, using: :gin, name: "documents_idx"

# Usage
Document.create(title: "Cats and Dogs", body: "are nice!")

## all documents matching 'cat & dog'
Document.where("textsearchable_index_col @@ to_tsquery(?)", "cat & dog")

9 資料庫視圖

假設您需要處理一個包含以下表格的舊式資料庫

rails_pg_guide=# \d "TBL_ART"
                                        Table "public.TBL_ART"
   Column   |            Type             |                         Modifiers
------------+-----------------------------+------------------------------------------------------------
 INT_ID     | integer                     | not null default nextval('"TBL_ART_INT_ID_seq"'::regclass)
 STR_TITLE  | character varying           |
 STR_STAT   | character varying           | default 'draft'::character varying
 DT_PUBL_AT | timestamp without time zone |
 BL_ARCH    | boolean                     | default false
Indexes:
    "TBL_ART_pkey" PRIMARY KEY, btree ("INT_ID")

此表格完全不符合 Rails 的慣例。由於簡單的 PostgreSQL 視圖預設是可更新的，我們可以將其包裝如下

# db/migrate/20131220144913_create_articles_view.rb
execute <<-SQL
CREATE VIEW articles AS
  SELECT "INT_ID" AS id,
         "STR_TITLE" AS title,
         "STR_STAT" AS status,
         "DT_PUBL_AT" AS published_at,
         "BL_ARCH" AS archived
  FROM "TBL_ART"
  WHERE "BL_ARCH" = 'f'
SQL

# app/models/article.rb
class Article < ApplicationRecord
  self.primary_key = "id"
  def archive!
    update_attribute :archived, true
  end
end

irb> first = Article.create! title: "Winter is coming", status: "published", published_at: 1.year.ago
irb> second = Article.create! title: "Brace yourself", status: "draft", published_at: 1.month.ago

irb> Article.count
=> 2
irb> first.archive!
irb> Article.count
=> 1

此應用程式只關心未歸檔的 Articles。視圖也允許設定條件，因此我們可以將已歸檔的 Articles 直接排除。

10 結構傾印

如果您的 config.active_record.schema_format 為 :sql，Rails 會呼叫 pg_dump 來產生結構傾印。

您可以使用 ActiveRecord::Tasks::DatabaseTasks.structure_dump_flags 來配置 pg_dump。例如，若要將註解從您的結構傾印中排除，請將此加入到初始化器中

ActiveRecord::Tasks::DatabaseTasks.structure_dump_flags = ["--no-comments"]
回到頂端