aboutsummaryrefslogtreecommitdiffstats
path: root/guides/source/active_record_postgresql.md
diff options
context:
space:
mode:
Diffstat (limited to 'guides/source/active_record_postgresql.md')
-rw-r--r--guides/source/active_record_postgresql.md80
1 files changed, 56 insertions, 24 deletions
diff --git a/guides/source/active_record_postgresql.md b/guides/source/active_record_postgresql.md
index 4d9c1776f4..9d495dfacb 100644
--- a/guides/source/active_record_postgresql.md
+++ b/guides/source/active_record_postgresql.md
@@ -29,8 +29,8 @@ 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)
+* [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
@@ -49,8 +49,8 @@ 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)
+* [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
@@ -83,7 +83,7 @@ Book.where("array_length(ratings, 1) >= 3")
### Hstore
-* [type definition](http://www.postgresql.org/docs/9.3/static/hstore.html)
+* [type definition](http://www.postgresql.org/docs/current/static/hstore.html)
NOTE: you need to enable the `hstore` extension to use hstore.
@@ -112,8 +112,8 @@ 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)
+* [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
@@ -138,10 +138,10 @@ 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)
+* [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.1.1/Range.html) objects.
+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
@@ -173,7 +173,7 @@ event.ends_at # => Thu, 13 Feb 2014
### Composite Types
-* [type definition](http://www.postgresql.org/docs/9.3/static/rowtypes.html)
+* [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:
@@ -213,7 +213,7 @@ contact.save!
### Enumerated Types
-* [type definition](http://www.postgresql.org/docs/9.3/static/datatype-enum.html)
+* [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:
@@ -242,14 +242,17 @@ 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)
+* [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.column :identifier, :uuid
+ t.uuid :identifier
end
# app/models/revision.rb
@@ -263,10 +266,35 @@ 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 < ActiveRecord::Base
+ has_many :comments
+end
+
+# app/models/comment.rb
+class Comment < ActiveRecord::Base
+ 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/9.3/static/datatype-bit.html)
-* [functions and operators](http://www.postgresql.org/docs/9.3/static/functions-bitstring.html)
+* [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
@@ -289,10 +317,10 @@ user.save!
### Network Address Types
-* [type definition](http://www.postgresql.org/docs/9.3/static/datatype-net-types.html)
+* [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.1.1/libdoc/ipaddr/rdoc/IPAddr.html)
+[`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
@@ -324,7 +352,7 @@ macbook.address
### Geometric Types
-* [type definition](http://www.postgresql.org/docs/9.3/static/datatype-geometric.html)
+* [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.
@@ -333,12 +361,13 @@ A point is casted to an array containing `x` and `y` coordinates.
UUID Primary Keys
-----------------
-NOTE: you need to enable the `uuid-ossp` extension to generate UUIDs.
+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 'uuid-ossp' unless extension_enabled?('uuid-ossp')
-create_table :devices, id: :uuid, default: 'uuid_generate_v4()' do |t|
+enable_extension 'pgcrypto' unless extension_enabled?('pgcrypto')
+create_table :devices, id: :uuid, default: 'gen_random_uuid()' do |t|
t.string :kind
end
@@ -351,6 +380,9 @@ 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
----------------
@@ -378,7 +410,7 @@ Document.where("to_tsvector('english', title || ' ' || body) @@ to_tsquery(?)",
Database Views
--------------
-* [view creation](http://www.postgresql.org/docs/9.3/static/sql-createview.html)
+* [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: