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 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 < 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 # 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! 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 # 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 < ApplicationRecord 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 < ApplicationRecord 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 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 # Usage Article.create status: "draft" article = Article.first article.status # => "draft" article.status = "published" 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 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 # Usage Revision.create identifier: "A0EEBC99-9C0B-4EF8-BB6D-6BB9BD380A11" revision = Revision.first 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, default: 'gen_random_uuid()' create_table :comments, id: :uuid, default: 'gen_random_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 # 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 < ApplicationRecord 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 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, default: 'gen_random_uuid()' do |t| t.string :kind end # app/models/device.rb class Device < ApplicationRecord end # Usage device = Device.create device.id # => "814865cd-5a1d-4771-9306-4268f188fe9e"
gen_random_uuid()
(from pgcrypto
) is assumed if no :default
option was
passed to create_table
.
3 Full Text Search
# 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")
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 < ApplicationRecord 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 # => 2 first.archive! Article.count # => 1
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 on the rubyonrails-docs mailing list.