Ruby on Rails 是一個使用 Ruby 語言編寫的開源 Web 應用程式框架。
PostgreSQL 是一個強大、開源的物件關聯式資料庫系統，擁有超過 35 年的活躍開發歷程，並以其可靠性、功能強大性和效能而享有盛譽。
PostgreSQL 提供許多特定資料類型，以下是 Rails 支援的 PostgreSQL 類型清單。
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
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")
3. Hstore
需要啟用 hstore 擴充功能才能使用 hstore。
# db/migrate/20131009135255_create_profiles.rb
class CreateProfiles < ActiveRecord::Migration[7.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"}>]>
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")
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
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!
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, to: :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"
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
8. UUID
如果你使用的是早於版本 13.0 的 PostgreSQL，你可能需要啟用特殊擴充功能才能使用 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
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!
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"
11. 幾何類型
除了 points 之外，所有幾何類型都會對應到一般文字。點會轉換為包含 x 和 y 座標的陣列。
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
