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 include non-key columns in indexes.
- How to use deferrable foreign keys.
- How to use unique constraints.
- How to implement exclusion constraints.
- 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 9.3installed. Older versions are not supported.
To get started with PostgreSQL have a look at theconfiguring 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.rbcreate_table:documentsdo|t|t.binary"payload"end# app/models/document.rbclassDocument<ApplicationRecordend# Usagedata=File.read(Rails.root+"tmp/output.pdf")Document.createpayload:data1.2. Array
# db/migrate/20140207133952_create_books.rbcreate_table:booksdo|t|t.string"title"t.string"tags",array:truet.integer"ratings",array:trueendadd_index:books,:tags,using:"gin"add_index:books,:ratings,using:"gin"# app/models/book.rbclassBook<ApplicationRecordend# UsageBook.createtitle:"Brave New World",tags:["fantasy","fiction"],ratings:[4,5]## Books for a single tagBook.where("'fantasy' = ANY (tags)")## Books for multiple tagsBook.where("tags @> ARRAY[?]::varchar[]",["fantasy","fiction"])## Books with 3 or more ratingsBook.where("array_length(ratings, 1) >= 3")1.3. Hstore
You need to enable thehstore extension to use hstore.
# db/migrate/20131009135255_create_profiles.rbclassCreateProfiles<ActiveRecord::Migration[8.1]enable_extension"hstore"unlessextension_enabled?("hstore")create_table:profilesdo|t|t.hstore"settings"endend# app/models/profile.rbclassProfile<ApplicationRecordendirb>Profile.create(settings:{"color"=>"blue","resolution"=>"800x600"})irb>profile=Profile.firstirb>profile.settings=>{"color"=>"blue","resolution"=>"800x600"}irb>profile.settings={"color"=>"yellow","resolution"=>"1280x1024"}irb>profile.save!irb>Profile.where("settings->'color' = ?","yellow")=>#<ActiveRecord::Relation[#<Profileid:1,settings:{"color"=>"yellow","resolution"=>"1280x1024"}>]>1.4. JSON and JSONB
# db/migrate/20131220144913_create_events.rb# ... for json datatype:create_table:eventsdo|t|t.json"payload"end# ... or for jsonb datatype:create_table:eventsdo|t|t.jsonb"payload"end# app/models/event.rbclassEvent<ApplicationRecordendirb>Event.create(payload:{kind:"user_renamed",change:["jack","john"]})irb>event=Event.firstirb>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 ->>returnstextirb>Event.where("payload->>'kind' = ?","user_renamed")1.5. Range Types
This type is mapped to RubyRange objects.
# db/migrate/20130923065404_create_events.rbcreate_table:eventsdo|t|t.daterange"duration"end# app/models/event.rbclassEvent<ApplicationRecordendirb>Event.create(duration:Date.new(2014,2,11)..Date.new(2014,2,12))irb>event=Event.firstirb>event.duration=>Tue,11Feb2014...Thu,13Feb2014## All Events on a given dateirb>Event.where("duration @> ?::date",Date.new(2014,2,12))## Working with range boundsirb>event=Event.select("lower(duration) AS starts_at").select("upper(duration) AS ends_at").firstirb>event.starts_at=>Tue,11Feb2014irb>event.ends_at=>Thu,13Feb20141.6. Composite Types
Currently there is no special support for composite types. They are mapped tonormal text columns:
CREATETYPEfull_addressAS(cityVARCHAR(90),streetVARCHAR(90));# db/migrate/20140207133952_create_contacts.rbexecute<<-SQL CREATE TYPE full_address AS ( city VARCHAR(90), street VARCHAR(90) );SQLcreate_table:contactsdo|t|t.column:address,:full_addressend# app/models/contact.rbclassContact<ApplicationRecordendirb>Contact.createaddress:"(Paris,Champs-Élysées)"irb>contact=Contact.firstirb>contact.address=>"(Paris,Champs-Élysées)"irb>contact.address="(Paris,Rue Basse)"irb>contact.save!1.7. Enumerated Types
The type can be mapped as a normal text column, or to anActiveRecord::Enum.
# db/migrate/20131220144913_create_articles.rbdefchangecreate_enum:article_status,["draft","published","archived"]create_table:articlesdo|t|t.enum:status,enum_type: :article_status,default:"draft",null:falseendendYou can also create an enum type and add an enum column to an existing table:
# db/migrate/20230113024409_add_status_to_articles.rbdefchangecreate_enum:article_status,["draft","published","archived"]add_column:articles,:status,:enum,enum_type: :article_status,default:"draft",null:falseendThe above migrations are both reversible, but you can define separate#up and#down methods if required. Make sure you remove any columns or tables that depend on the enum type before dropping it:
defdowndrop_table:articles# OR: remove_column :articles, :statusdrop_enum:article_statusendDeclaring an enum attribute in the model adds helper methods and prevents invalid values from being assigned to instances of the class:
# app/models/article.rbclassArticle<ApplicationRecordenum:status,{draft:"draft",published:"published",archived:"archived"},prefix:trueendirb>article=Article.createirb>article.status=>"draft"# default status from PostgreSQL, as defined in migration aboveirb>article.status_published!irb>article.status=>"published"irb>article.status_archived?=>falseirb>article.status="deleted"ArgumentError: 'deleted' is not a valid statusTo rename the enum you can userename_enum along with updating any modelusage:
# db/migrate/20150718144917_rename_article_status.rbdefchangerename_enum:article_status,:article_stateendTo add a new value you can useadd_enum_value:
# db/migrate/20150720144913_add_new_state_to_articles.rbdefupadd_enum_value:article_state,"archived"# will be at the end after publishedadd_enum_value:article_state,"in review",before:"published"add_enum_value:article_state,"approved",after:"in review"add_enum_value:article_state,"rejected",if_not_exists:true# won't raise an error if the value already existsendEnum values can't be dropped, which also means add_enum_value is irreversible. You can read whyhere.
To rename a value you can userename_enum_value:
# db/migrate/20150722144915_rename_article_state.rbdefchangerename_enum_value:article_state,from:"archived",to:"deleted"endHint: to show all the values of the all enums you have, you can call this query inbin/rails db orpsql console:
SELECTn.nspnameASenum_schema,t.typnameASenum_name,e.enumlabelASenum_valueFROMpg_typetJOINpg_enumeONt.oid=e.enumtypidJOINpg_catalog.pg_namespacenONn.oid=t.typnamespace1.8. UUID
If you're using PostgreSQL earlier than version 13.0 you may need to enable special extensions to use UUIDs. Enable thepgcrypto extension (PostgreSQL >= 9.4) oruuid-ossp extension (for even earlier releases).
# db/migrate/20131220144913_create_revisions.rbcreate_table:revisionsdo|t|t.uuid:identifierend# app/models/revision.rbclassRevision<ApplicationRecordendirb>Revision.createidentifier:"A0EEBC99-9C0B-4EF8-BB6D-6BB9BD380A11"irb>revision=Revision.firstirb>revision.identifier=>"a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11"You can useuuid type to define references in migrations:
# db/migrate/20150418012400_create_blog.rbenable_extension"pgcrypto"unlessextension_enabled?("pgcrypto")create_table:posts,id: :uuidcreate_table:comments,id: :uuiddo|t|# t.belongs_to :post, type: :uuidt.references:post,type: :uuidend# app/models/post.rbclassPost<ApplicationRecordhas_many:commentsend# app/models/comment.rbclassComment<ApplicationRecordbelongs_to:postendSeethis section for more details on using UUIDs as primary key.
1.9. Bit String Types
# db/migrate/20131220144913_create_users.rbcreate_table:users,force:truedo|t|t.column:settings,"bit(8)"end# app/models/user.rbclassUser<ApplicationRecordendirb>User.createsettings:"01010011"irb>user=User.firstirb>user.settings=>"01010011"irb>user.settings="0xAF"irb>user.settings=>"10101111"irb>user.save!1.10. Network Address Types
The typesinet andcidr are mapped to RubyIPAddrobjects. Themacaddr type is mapped to normal text.
# db/migrate/20140508144913_create_devices.rbcreate_table(:devices,force:true)do|t|t.inet"ip"t.cidr"network"t.macaddr"address"end# app/models/device.rbclassDevice<ApplicationRecordendirb>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 ofpoints are mapped to normal text.A point is cast to an array containingx andy coordinates.
1.12. Interval
This type is mapped toActiveSupport::Duration objects.
# db/migrate/20200120000000_create_events.rbcreate_table:eventsdo|t|t.interval"duration"end# app/models/event.rbclassEvent<ApplicationRecordendirb>Event.create(duration:2.days)irb>event=Event.firstirb>event.duration=>2days1.13. Timestamps
Rails migrations with timestamps store the time a model was created or updated. By default and for legacy reasons, the columns use thetimestamp without time zone data type.
# db/migrate/20241220144913_create_devices.rbcreate_table:post,id: :uuiddo|t|t.datetime:published_at# By default, Active Record will set the data type of this column to `timestamp without time zone`.endWhile this works ok,PostgreSQL best practices recommend thattimestamp with time zone is used instead for timezone-aware timestamps.This must be configured before it can be used for new migrations.
To configuretimestamp with time zone as your new timestamp default data type, place the following configuration in theconfig/application.rb file.
# config/application.rbActiveSupport.on_load(:active_record_postgresqladapter)doself.datetime_type=:timestamptzendWith that configuration in place, generate and apply new migrations, then verify their timestamps use thetimestamp with time zone data type.
2. UUID Primary Keys
You need to enable thepgcrypto (only PostgreSQL >= 9.4) oruuid-osspextension to generate random UUIDs.
# db/migrate/20131220144913_create_devices.rbenable_extension"pgcrypto"unlessextension_enabled?("pgcrypto")create_table:devices,id: :uuiddo|t|t.string:kindend# app/models/device.rbclassDevice<ApplicationRecordendirb>device=Device.createirb>device.id=>"814865cd-5a1d-4771-9306-4268f188fe9e"gen_random_uuid() (frompgcrypto) is assumed if no:default optionwas passed tocreate_table.
To use the Rails model generator for a table using UUID as the primary key, pass--primary-key-type=uuid to the model generator.
For example:
$bin/railsgenerate model Device--primary-key-type=uuid kind:stringWhen building a model with a foreign key that will reference this UUID, treatuuid as the native field type, for example:
$bin/railsgenerate model Case device_id:uuid3. Indexing
PostgreSQL includes a variety of index options. The following options aresupported by the PostgreSQL adapter in addition to thecommon index options
3.1. Include
When creating a new index, non-key columns can be included with the:include option.These keys are not used in index scans for searching, but can be read during an indexonly scan without having to visit the associated table.
# db/migrate/20131220144913_add_index_users_on_email_include_id.rbadd_index:users,:email,include: :idMultiple columns are supported:
# db/migrate/20131220144913_add_index_users_on_email_include_id_and_created_at.rbadd_index:users,:email,include:[:id,:created_at]4. Generated Columns
Generated columns are supported since version 12.0 of PostgreSQL.
# db/migrate/20131220144913_create_users.rbcreate_table:usersdo|t|t.string:namet.virtual:name_upcased,type: :string,as:"upper(name)",stored:trueend# app/models/user.rbclassUser<ApplicationRecordend# Usageuser=User.create(name:"John")User.last.name_upcased# => "JOHN"5. Deferrable Foreign Keys
By default, table constraints in PostgreSQL are checked immediately after each statement. It intentionally does not allow creating records where the referenced record is not yet in the referenced table. It is possible to run this integrity check later on when the transaction is committed by addingDEFERRABLE to the foreign key definition though. To defer all checks by default it can be set toDEFERRABLE INITIALLY DEFERRED. Rails exposes this PostgreSQL feature by adding the:deferrable key to theforeign_key options in theadd_reference andadd_foreign_key methods.
One example of this is creating circular dependencies in a transaction even if you have created foreign keys:
add_reference:person,:alias,foreign_key:{deferrable: :deferred}add_reference:alias,:person,foreign_key:{deferrable: :deferred}If the reference was created with theforeign_key: true option, the following transaction would fail when executing the firstINSERT statement. It does not fail when thedeferrable: :deferred option is set though.
ActiveRecord::Base.lease_connection.transactiondoperson=Person.create(id:SecureRandom.uuid,alias_id:SecureRandom.uuid,name:"John Doe")Alias.create(id:person.alias_id,person_id:person.id,name:"jaydee")endWhen the:deferrable option is set to:immediate, let the foreign keys keep the default behavior of checking the constraint immediately, but allow manually deferring the checks usingset_constraints within a transaction. This will cause the foreign keys to be checked when the transaction is committed:
ActiveRecord::Base.lease_connection.transactiondoActiveRecord::Base.lease_connection.set_constraints(:deferred)person=Person.create(alias_id:SecureRandom.uuid,name:"John Doe")Alias.create(id:person.alias_id,person_id:person.id,name:"jaydee")endBy default:deferrable isfalse and the constraint is always checked immediately.
6. Unique Constraint
# db/migrate/20230422225213_create_items.rbcreate_table:itemsdo|t|t.integer:position,null:falset.unique_constraint[:position],deferrable: :immediateendIf you want to change an existing unique index to deferrable, you can use:using_index to create deferrable unique constraints.
add_unique_constraint:items,deferrable: :deferred,using_index:"index_items_on_position"Like foreign keys, unique constraints can be deferred by setting:deferrable to either:immediate or:deferred. By default,:deferrable isfalse and the constraint is always checked immediately.
7. Exclusion Constraints
# db/migrate/20131220144913_create_products.rbcreate_table:productsdo|t|t.integer:price,null:falset.daterange:availability_range,null:falset.exclusion_constraint"price WITH =, availability_range WITH &&",using: :gist,name:"price_check"endLike foreign keys, exclusion constraints can be deferred by setting:deferrable to either:immediate or:deferred. By default,:deferrable isfalse and the constraint is always checked immediately.
8. Full Text Search
# db/migrate/20131220144913_create_documents.rbcreate_table:documentsdo|t|t.string:titlet.string:bodyendadd_index:documents,"to_tsvector('english', title || ' ' || body)",using: :gin,name:"documents_idx"# app/models/document.rbclassDocument<ApplicationRecordend# UsageDocument.create(title:"Cats and Dogs",body:"are nice!")## all documents matching 'cat & dog'Document.where("to_tsvector('english', title || ' ' || body) @@ to_tsquery(?)","cat & dog")Optionally, you can store the vector as automatically generated column (from PostgreSQL 12.0):
# db/migrate/20131220144913_create_documents.rbcreate_table:documentsdo|t|t.string:titlet.string:bodyt.virtual:textsearchable_index_col,type: :tsvector,as:"to_tsvector('english', title || ' ' || body)",stored:trueendadd_index:documents,:textsearchable_index_col,using: :gin,name:"documents_idx"# UsageDocument.create(title:"Cats and Dogs",body:"are nice!")## all documents matching 'cat & dog'Document.where("textsearchable_index_col @@ to_tsquery(?)","cat & dog")9. 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 falseIndexes: "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.rbexecute<<-SQLCREATE 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.rbclassArticle<ApplicationRecordself.primary_key="id"defarchive!update_attribute:archived,trueendendirb>first=Article.create!title:"Winter is coming",status:"published",published_at:1.year.agoirb>second=Article.create!title:"Brace yourself",status:"draft",published_at:1.month.agoirb>Article.count=>2irb>first.archive!irb>Article.count=>1This application only cares about non-archivedArticles. A view alsoallows for conditions so we can exclude the archivedArticles directly.
10. Structure Dumps
If yourconfig.active_record.schema_format is:sql, Rails will callpg_dump to generate astructure dump.
You can useActiveRecord::Tasks::DatabaseTasks.structure_dump_flags to configurepg_dump.For example, to exclude comments from your structure dump, add this to an initializer:
ActiveRecord::Tasks::DatabaseTasks.structure_dump_flags=["--no-comments"]11. Explain
Along with the standardexplain options, the PostgreSQL adapter supportsbuffers.
Company.where(id:owning_companies_ids).explain(:analyze,:buffers)#=> EXPLAIN (ANALYZE, BUFFERS) SELECT "companies".* FROM "companies"# ...# Seq Scan on companies (cost=0.00..2.21 rows=3 width=64)# ...See their documentation for more details.