diff options
Diffstat (limited to 'guides/source/active_record_postgresql.md')
-rw-r--r-- | guides/source/active_record_postgresql.md | 309 |
1 files changed, 309 insertions, 0 deletions
diff --git a/guides/source/active_record_postgresql.md b/guides/source/active_record_postgresql.md new file mode 100644 index 0000000000..f452b5c338 --- /dev/null +++ b/guides/source/active_record_postgresql.md @@ -0,0 +1,309 @@ +eActive Record and PostgreSQL +============================ + +This guide goes over PostgreSQL specific usage of Active Record. + +How to use the PostgreSQL adapter for Active Record is described in the +[configuring Rails guide](configuring.html#configuring-a-postgresql-database). + +In order to use the PostgreSQL adapter you need to have at least version 8.2 +installed. Older versions are not supported. + +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/9.3/static/datatype-binary.html) +* [functions and operators](http://www.postgresql.org/docs/9.3/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 < ActiveRecord::Base +end + +# Usage +data = File.read(Rails.root + "tmp/output.pdf") +Document.create payload: data +``` + +### Array + + * [type definition](http://www.postgresql.org/docs/9.3/static/arrays.html) + * [functions and operators](http://www.postgresql.org/docs/9.3/static/functions-array.html) + +```ruby +# db/migrate/20140207133952_create_books.rb +create_table :book do |t| + t.string 'title' + t.string 'tags', array: true + t.integer 'ratings', array: true +end + +# 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") +``` + +### Hstore + + * [type definition](http://www.postgresql.org/docs/9.3/static/hstore.html) + +```ruby +# 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", "resulution" => "1280x1024"} +profile.save! + +## you need to call _will_change! if you are editing the store in place +profile.settings["color"] = "green" +profile.settings_will_change! +profile.save! +``` + +### Json + + * [type definition](http://www.postgresql.org/docs/9.3/static/datatype-json.html) + * [functions and operators](http://www.postgresql.org/docs/9.3/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 < 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 +Event.where("payload->'kind' = ?", "user_renamed") +``` + +### Range Types + + * [type definition](http://www.postgresql.org/docs/9.3/static/rangetypes.html) + * [functions and operators](http://www.postgresql.org/docs/9.3/static/functions-range.html) + +This type is mapped to Ruby [`Range`]() objects. + +```ruby +# 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 +``` + +### Composite Types + + * [type definition](http://www.postgresql.org/docs/9.3/static/rowtypes.html) + +Currently there is no special support for composite types. They are mapped to as +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 < 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! +``` + +### Enumerated Types + + * [type definition](http://www.postgresql.org/docs/9.3/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_events.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! +``` + +### UUID + + * [type definition](http://www.postgresql.org/docs/9.3/static/datatype-uuid.html) + * [generator functions](http://www.postgresql.org/docs/9.3/static/uuid-ossp.html) + + +```ruby +# 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" +``` + +#### as primary key + +```ruby +# 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" +``` + +### Bit String Types + + * [type definition](http://www.postgresql.org/docs/9.3/static/datatype-bit.html) + * [functions and operators](http://www.postgresql.org/docs/9.3/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 < ActiveRecord::Base +end + +# Usage +User.create settings: "01010011" +user = User.first +user.settings # => "(Paris,Champs-Élysées)" +user.settings = "0xAF" +user.settings # => 10101111 +user.save! +``` + +### Network Address Types + + * [type definition](http://www.postgresql.org/docs/9.3/static/datatype-net-types.html) + +The types `inet` and `cidr` are mapped to Ruby [`IPAddr`]() objects. The +`macaddr` type is mapped to normal text. + +### Geometric Types + + * [type definition](http://www.postgresql.org/docs/9.3/static/datatype-geometric.html) + +All geometric types are mapped to normal text. + +Views +----- |