Active Record and PostgreSQL

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

type definition

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

type definition

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

type definition

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

type definition

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

criação da visão

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.

Guiarails.com.br

Chapters