**DO NOT READ THIS FILE ON GITHUB, GUIDES ARE PUBLISHED ON http://guides.rubyonrails.org.** Active Record and PostgreSQL ============================ This guide covers PostgreSQL specific usage of Active Record. After reading this guide, you will know: * How to use PostgreSQL's datatypes. * How to use UUID primary keys. * How to implement full text search with PostgreSQL. * How to back your Active Record models with database views. -------------------------------------------------------------------------------- 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](configuring.html#configuring-a-postgresql-database). It describes how to properly setup Active Record for PostgreSQL. Datatypes --------- PostgreSQL offers a number of specific datatypes. Following is a list of types, that are supported by the PostgreSQL adapter. ### Bytea * [type definition](http://www.postgresql.org/docs/current/static/datatype-binary.html) * [functions and operators](http://www.postgresql.org/docs/current/static/functions-binarystring.html) ```ruby # 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 ``` ### Array * [type definition](http://www.postgresql.org/docs/current/static/arrays.html) * [functions and operators](http://www.postgresql.org/docs/current/static/functions-array.html) ```ruby # 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") ``` ### Hstore * [type definition](http://www.postgresql.org/docs/current/static/hstore.html) * [functions and operators](http://www.postgresql.org/docs/current/static/hstore.html#AEN167712) NOTE: You need to enable the `hstore` extension to use hstore. ```ruby # 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") #=> #"yellow", "resolution"=>"1280x1024"}>]> ``` ### JSON * [type definition](http://www.postgresql.org/docs/current/static/datatype-json.html) * [functions and operators](http://www.postgresql.org/docs/current/static/functions-json.html) ```ruby # db/migrate/20131220144913_create_events.rb create_table :events do |t| t.json '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") ``` ### Range Types * [type definition](http://www.postgresql.org/docs/current/static/rangetypes.html) * [functions and operators](http://www.postgresql.org/docs/current/static/functions-range.html) This type is mapped to Ruby [`Range`](http://www.ruby-doc.org/core-2.2.2/Range.html) objects. ```ruby # 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 ``` ### Composite Types * [type definition](http://www.postgresql.org/docs/current/static/rowtypes.html) Currently there is no special support for composite types. They are mapped to normal text columns: ```sql CREATE TYPE full_address AS ( city VARCHAR(90), street VARCHAR(90) ); ``` ```ruby # 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! ``` ### Enumerated Types * [type definition](http://www.postgresql.org/docs/current/static/datatype-enum.html) Currently there is no special support for enumerated types. They are mapped as normal text columns: ```ruby # 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](http://www.postgresql.org/docs/current/static/sql-altertype.html): ```ruby # 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 ``` NOTE: ENUM values can't be dropped currently. You can read why [here](http://www.postgresql.org/message-id/29F36C7C98AB09499B1A209D48EAA615B7653DBC8A@mail2a.alliedtesting.com). Hint: to show all the values of the all enums you have, you should call this query in `bin/rails db` or `psql` console: ```sql 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 ``` ### UUID * [type definition](http://www.postgresql.org/docs/current/static/datatype-uuid.html) * [pgcrypto generator function](http://www.postgresql.org/docs/current/static/pgcrypto.html#AEN159361) * [uuid-ossp generator functions](http://www.postgresql.org/docs/current/static/uuid-ossp.html) NOTE: You need to enable the `pgcrypto` (only PostgreSQL >= 9.4) or `uuid-ossp` extension to use uuid. ```ruby # 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: ```ruby # 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](#uuid-primary-keys) for more details on using UUIDs as primary key. ### Bit String Types * [type definition](http://www.postgresql.org/docs/current/static/datatype-bit.html) * [functions and operators](http://www.postgresql.org/docs/current/static/functions-bitstring.html) ```ruby # 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 < ApplicationRecord end # Usage User.create settings: "01010011" user = User.first user.settings # => "01010011" user.settings = "0xAF" user.settings # => 10101111 user.save! ``` ### Network Address Types * [type definition](http://www.postgresql.org/docs/current/static/datatype-net-types.html) The types `inet` and `cidr` are mapped to Ruby [`IPAddr`](http://www.ruby-doc.org/stdlib-2.2.2/libdoc/ipaddr/rdoc/IPAddr.html) objects. The `macaddr` type is mapped to normal text. ```ruby # 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 # => # macbook.network # => # macbook.address # => "32:01:16:6d:05:ef" ``` ### Geometric Types * [type definition](http://www.postgresql.org/docs/current/static/datatype-geometric.html) 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. UUID Primary Keys ----------------- NOTE: You need to enable the `pgcrypto` (only PostgreSQL >= 9.4) or `uuid-ossp` extension to generate random UUIDs. ```ruby # 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" ``` NOTE: `uuid_generate_v4()` (from `uuid-ossp`) is assumed if no `:default` option was passed to `create_table`. Full Text Search ---------------- ```ruby # 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 < 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") ``` Database Views -------------- * [view creation](http://www.postgresql.org/docs/current/static/sql-createview.html) 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: ```ruby # 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 # => 1 first.archive! Article.count # => 2 ``` NOTE: This application only cares about non-archived `Articles`. A view also allows for conditions so we can exclude the archived `Articles` directly.