In order to use the PostgreSQL adapter you need to have at least version 9.3 installed. Older versions are not supported.
To get started with PostgreSQL have a look at the configuring Rails guide. It describes how to properly set up Active Record for PostgreSQL.
1 Datatypes
PostgreSQL offers a number of specific datatypes. Following is a list of types, that are supported by the PostgreSQL adapter.
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 Array
# 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
You need to enable the hstore
extension to use hstore.
# db/migrate/20131009135255_create_profiles.rb
ActiveRecord::Schema.define do
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 and 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 Range Types
This type is mapped to Ruby Range
objects.
# 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 Composite Types
Currently there is no special support for composite types. They are mapped to normal text columns:
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 Enumerated Types
Currently there is no special support for enumerated types. They are mapped as normal text columns:
# db/migrate/20131220144913_create_articles.rb
def up
execute <<-SQL
CREATE TYPE article_status AS ENUM ('draft', 'published');
SQL
create_table :articles do |t|
t.column :status, :article_status
end
end
# NOTE: It's important to drop table before dropping enum.
def down
drop_table :articles
execute <<-SQL
DROP TYPE article_status;
SQL
end
# app/models/article.rb
class Article < ApplicationRecord
end
irb> Article.create status: "draft"
irb> article = Article.first
irb> article.status
=> "draft"
irb> article.status = "published"
irb> article.save!
To add a new value before/after existing one you should use ALTER TYPE:
# db/migrate/20150720144913_add_new_state_to_articles.rb
# NOTE: ALTER TYPE ... ADD VALUE cannot be executed inside of a transaction block so here we are using disable_ddl_transaction!
disable_ddl_transaction!
def up
execute <<-SQL
ALTER TYPE article_status ADD VALUE IF NOT EXISTS 'archived' AFTER 'published';
SQL
end
ENUM values can't be dropped currently. You can read why here.
Hint: to show all the values of the all enums you have, you should call this query in bin/rails db
or psql
console:
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
You need to enable the pgcrypto
(only PostgreSQL >= 9.4) or uuid-ossp
extension to use uuid.
# 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"
You can use uuid
type to define references in migrations:
# 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
See this section for more details on using UUIDs as primary key.
1.9 Bit String Types
# 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 Network Address Types
The types inet
and cidr
are mapped to Ruby
IPAddr
objects. The macaddr
type is mapped to normal text.
# 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 Geometric Types
All geometric types, with the exception of points
are mapped to normal text.
A point is casted to an array containing x
and y
coordinates.
1.12 Interval
This type is mapped to ActiveSupport::Duration
objects.
# 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 Primary Keys
You need to enable the pgcrypto
(only PostgreSQL >= 9.4) or uuid-ossp
extension to generate random UUIDs.
# 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"
gen_random_uuid()
(from pgcrypto
) is assumed if no :default
option was
passed to create_table
.
3 Generated Columns
Generated columns are supported since version 12.0 of PostgreSQL.
# 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"
4 Pesquisa de Texto Completo
# 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
# Utilização
Document.create(title: "Cats and Dogs", body: "are nice!")
## todos os documentos que correspondem a 'cat & dog'
Document.where("to_tsvector('english', title || ' ' || body) @@ to_tsquery(?)",
"cat & dog")
Opcionalmente, você pode armazenar o vetor como coluna gerada automaticamente (do 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'
# Uso
Document.create(title: "Cats and Dogs", body: "are nice!")
## todos os documentos contendo 'cat & dog'
Document.where("textsearchable_index_col @@ to_tsquery(?)", "cat & dog")
5 Visão de Banco de Dados
Imagine que você precisa trabalhar com um banco de dados legado contendo as seguintes tabelas:
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")
Esta tabela certamente não segue as convenções do Rails. Como as visões no PostgreSQL são atualizáveis por padrão, nós podemos envolver isso da seguinte maneira:
# 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
Esta aplicação só se importa com Articles
não arquivados. Uma visão também
permite condições para que possamos excluir os Articles
arquivados diretamente.
Feedback
Você é incentivado a ajudar a melhorar a qualidade deste guia.
Por favor, contribua caso veja quaisquer erros, inclusive erros de digitação. Para começar, você pode ler nossa sessão de contribuindo com a documentação.
Você também pode encontrar conteúdo incompleto ou coisas que não estão atualizadas. Por favor, adicione qualquer documentação em falta na main do Rails. Certifique-se de checar o Edge Guides (en-US) primeiro para verificar se o problema já foi resolvido ou não no branch main. Verifique as Diretrizes do Guia Ruby on Rails para estilo e convenções.
Se, por qualquer motivo, você encontrar algo para consertar, mas não conseguir consertá-lo, por favor abra uma issue no nosso Guia.
E por último, mas não menos importante, qualquer tipo de discussão sobre a documentação do Ruby on Rails é muito bem vinda na lista de discussão rubyonrails-docs e nas issues do Guia em português.