More at rubyonrails.org: More Ruby on Rails

Active Record and PostgreSQL

This guide covers PostgreSQL specific usage of Active Record.

After reading this guide, you will know:

In order to use the PostgreSQL adapter you need to have at least version 8.2 installed. Older versions are not supported.

To get started with PostgreSQL have a look at the configuring Rails guide. It describes how to properly setup 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 < ActiveRecord::Base
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 < 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")

1.3 Hstore

# db/migrate/20131009135255_create_profiles.rb
ActiveRecord::Schema.define do
  create_table :profiles do |t|
    t.hstore 'settings'
  end
end
 
# app/models/profile.rb
class Profile < ActiveRecord::Base
end
 
# 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!

1.4 JSON

# db/migrate/20131220144913_create_events.rb
create_table :events do |t|
  t.json 'payload'
end
 
# app/models/event.rb
class Event < ActiveRecord::Base
end
 
# Usage
Event.create(payload: { kind: "user_renamed", change: ["jack", "john"]})
 
event = Event.first
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
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 < ActiveRecord::Base
end
 
# Usage
Event.create(duration: Date.new(2014, 2, 11)..Date.new(2014, 2, 12))
 
event = Event.first
event.duration # => Tue, 11 Feb 2014...Thu, 13 Feb 2014
 
## All Events on a given date
Event.where("duration @> ?::date", Date.new(2014, 2, 12))
 
## Working with range bounds
event = Event.
  select("lower(duration) AS starts_at").
  select("upper(duration) AS ends_at").first
 
event.starts_at # => Tue, 11 Feb 2014
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 < ActiveRecord::Base
end
 
# Usage
Contact.create address: "(Paris,Champs-Élysées)"
contact = Contact.first
contact.address # => "(Paris,Champs-Élysées)"
contact.address = "(Paris,Rue Basse)"
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
execute <<-SQL
  CREATE TYPE article_status AS ENUM ('draft', 'published');
SQL
create_table :articles do |t|
  t.column :status, :article_status
end
 
# app/models/article.rb
class Article < ActiveRecord::Base
end
 
# Usage
Article.create status: "draft"
article = Article.first
article.status # => "draft"
 
article.status = "published"
article.save!

1.8 UUID

# db/migrate/20131220144913_create_revisions.rb
create_table :revisions do |t|
  t.column :identifier, :uuid
end
 
# app/models/revision.rb
class Revision < ActiveRecord::Base
end
 
# Usage
Revision.create identifier: "A0EEBC99-9C0B-4EF8-BB6D-6BB9BD380A11"
 
revision = Revision.first
revision.identifier # => "a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11"

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/device.rb
class User < ActiveRecord::Base
end
 
# Usage
User.create settings: "01010011"
user = User.first
user.settings # => "01010011"
user.settings = "0xAF"
user.settings # => 10101111
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 < ActiveRecord::Base
end
 
# Usage
macbook = Device.create(ip: "192.168.1.12",
                        network: "192.168.2.0/24",
                        address: "32:01:16:6d:05:ef")
 
macbook.ip
# => #<IPAddr: IPv4:192.168.1.12/255.255.255.255>
 
macbook.network
# => #<IPAddr: IPv4:192.168.2.0/255.255.255.0>
 
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.

2 UUID Primary Keys

you need to enable the uuid-ossp extension to generate UUIDs.

# db/migrate/20131220144913_create_devices.rb
enable_extension 'uuid-ossp' unless extension_enabled?('uuid-ossp')
create_table :devices, id: :uuid, default: 'uuid_generate_v4()' do |t|
  t.string :kind
end
 
# app/models/device.rb
class Device < ActiveRecord::Base
end
 
# Usage
device = Device.create
device.id # => "814865cd-5a1d-4771-9306-4268f188fe9e"

3 Full Text Search

# db/migrate/20131220144913_create_documents.rb
create_table :documents do |t|
  t.string 'title'
  t.string 'body'
end
 
execute "CREATE INDEX documents_idx ON documents USING gin(to_tsvector('english', title || ' ' || body));"
 
# app/models/document.rb
class Document < ActiveRecord::Base
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")

4 Database Views

Imagine you need to work with a legacy database containing the following table:

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

This table does not follow the Rails conventions at all. Because simple PostgreSQL views are updateable by default, we can wrap it as follows:

# 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 < ActiveRecord::Base
  self.primary_key = "id"
  def archive!
    update_attribute :archived, true
  end
end
 
# Usage
first = Article.create! title: "Winter is coming",
                        status: "published",
                        published_at: 1.year.ago
second = Article.create! title: "Brace yourself",
                         status: "draft",
                         published_at: 1.month.ago
 
Article.count # => 1
first.archive!
Article.count # => 2

This application only cares about non-archived Articles. A view also allows for conditions so we can exclude the archived Articles directly.

Feedback

You're encouraged to help improve the quality of this guide.

Please contribute if you see any typos or factual errors. To get started, you can read our documentation contributions section.

You may also find incomplete content, or stuff that is not up to date. Please do add any missing documentation for master. Make sure to check Edge Guides first to verify if the issues are already fixed or not on the master branch. Check the Ruby on Rails Guides Guidelines for style and conventions.

If for whatever reason you spot something to fix but cannot patch it yourself, please open an issue.

And last but not least, any kind of discussion regarding Ruby on Rails documentation is very welcome in the rubyonrails-docs mailing list.