/
active_record_postgresql.html
716 lines (636 loc) · 27.9 KB
/
active_record_postgresql.html
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1">
<title>Active Record and PostgreSQL — Ruby on Rails Guides</title>
<link rel="stylesheet" type="text/css" href="stylesheets/style.css" data-turbolinks-track="reload">
<link rel="stylesheet" type="text/css" href="stylesheets/print.css" media="print">
<link rel="stylesheet" type="text/css" href="stylesheets/syntaxhighlighter/shCore.css" data-turbolinks-track="reload">
<link rel="stylesheet" type="text/css" href="stylesheets/syntaxhighlighter/shThemeRailsGuides.css" data-turbolinks-track="reload">
<link rel="stylesheet" type="text/css" href="stylesheets/fixes.css" data-turbolinks-track="reload">
<link href="images/favicon.ico" rel="shortcut icon" type="image/x-icon" />
<script src="javascripts/syntaxhighlighter.js" data-turbolinks-track="reload"></script>
<script src="javascripts/turbolinks.js" data-turbolinks-track="reload"></script>
<script src="javascripts/guides.js" data-turbolinks-track="reload"></script>
<script src="javascripts/responsive-tables.js" data-turbolinks-track="reload"></script>
</head>
<body class="guide">
<div>
<img src="images/edge_badge.png" alt="edge-badge" id="edge-badge" />
</div>
<div id="topNav">
<div class="wrapper">
<strong class="more-info-label">Veja mais em <a href="http://rubyonrails.org/">rubyonrails.org:</a> </strong>
<span class="red-button more-info-button">
Mais Ruby on Rails
</span>
<ul class="more-info-links s-hidden">
<li class="more-info"><a href="https://weblog.rubyonrails.org/">Blog</a></li>
<li class="more-info"><a href="https://guides.rubyonrails.org/">Guia</a></li>
<li class="more-info"><a href="http://api.rubyonrails.org/">API</a></li>
<li class="more-info"><a href="https://stackoverflow.com/questions/tagged/ruby-on-rails">Pedir ajuda</a></li>
<li class="more-info"><a href="https://github.com/rails/rails">Contribuir no GitHub</a></li>
</ul>
</div>
</div>
<div id="header">
<div class="wrapper clearfix">
<h1><a href="index.html" title="Return to home page">Guides.rubyonrails.org</a></h1>
<ul class="nav">
<li><a class="nav-item" href="index.html">Home</a></li>
<li class="guides-index guides-index-large">
<a href="index.html" id="guidesMenu" class="guides-index-item nav-item">Guias</a>
<div id="guides" class="clearfix" style="display: none;">
<hr />
<div class="guides-section-container">
<div class="guides-section">
<dt>Comece Aqui</dt>
</div>
<div class="guides-section">
<dt>Models</dt>
</div>
<div class="guides-section">
<dt>Views</dt>
</div>
<div class="guides-section">
<dt>Controllers</dt>
</div>
<div class="guides-section">
<dt>Other Components</dt>
</div>
<div class="guides-section">
<dt>Digging Deeper</dt>
</div>
<div class="guides-section">
<dt>Extending Rails</dt>
<dd><a href="rails_on_rack.html">Rails on Rack</a></dd>
<dd><a href="generators.html">Creating and Customizing Rails Generators & Templates</a></dd>
</div>
<div class="guides-section">
<dt>Contributions</dt>
<dd><a href="contributing_to_ruby_on_rails.html">Contributing to Ruby on Rails</a></dd>
<dd><a href="api_documentation_guidelines.html">API Documentation Guidelines</a></dd>
<dd><a href="ruby_on_rails_guides_guidelines.html">Guides Guidelines</a></dd>
</div>
<div class="guides-section">
<dt>Policies</dt>
<dd><a href="maintenance_policy.html">Maintenance Policy</a></dd>
</div>
<div class="guides-section">
<dt>Release Notes</dt>
<dd><a href="upgrading_ruby_on_rails.html">Upgrading Ruby on Rails</a></dd>
<dd><a href="5_2_release_notes.html">Version 5.2 - April 2018</a></dd>
<dd><a href="5_1_release_notes.html">Version 5.1 - April 2017</a></dd>
<dd><a href="5_0_release_notes.html">Version 5.0 - June 2016</a></dd>
<dd><a href="4_2_release_notes.html">Version 4.2 - December 2014</a></dd>
<dd><a href="4_1_release_notes.html">Version 4.1 - April 2014</a></dd>
<dd><a href="4_0_release_notes.html">Version 4.0 - June 2013</a></dd>
<dd><a href="3_2_release_notes.html">Version 3.2 - January 2012</a></dd>
<dd><a href="3_1_release_notes.html">Version 3.1 - August 2011</a></dd>
<dd><a href="3_0_release_notes.html">Version 3.0 - August 2010</a></dd>
<dd><a href="2_3_release_notes.html">Version 2.3 - March 2009</a></dd>
<dd><a href="2_2_release_notes.html">Version 2.2 - November 2008</a></dd>
</div>
</div>
</div>
</li>
<li><a class="nav-item" href="contributing_to_ruby_on_rails.html">Contribua</a></li>
<li class="guides-index guides-index-small">
<select class="guides-index-item nav-item">
<option value="index.html">Guias</option>
<optgroup label="Comece Aqui">
</optgroup>
<optgroup label="Models">
</optgroup>
<optgroup label="Views">
</optgroup>
<optgroup label="Controllers">
</optgroup>
<optgroup label="Other Components">
</optgroup>
<optgroup label="Digging Deeper">
</optgroup>
<optgroup label="Extending Rails">
<option value="rails_on_rack.html">Rails on Rack</option>
<option value="generators.html">Creating and Customizing Rails Generators & Templates</option>
</optgroup>
<optgroup label="Contributions">
<option value="contributing_to_ruby_on_rails.html">Contributing to Ruby on Rails</option>
<option value="api_documentation_guidelines.html">API Documentation Guidelines</option>
<option value="ruby_on_rails_guides_guidelines.html">Guides Guidelines</option>
</optgroup>
<optgroup label="Policies">
<option value="maintenance_policy.html">Maintenance Policy</option>
</optgroup>
<optgroup label="Release Notes">
<option value="upgrading_ruby_on_rails.html">Upgrading Ruby on Rails</option>
<option value="5_2_release_notes.html">Version 5.2 - April 2018</option>
<option value="5_1_release_notes.html">Version 5.1 - April 2017</option>
<option value="5_0_release_notes.html">Version 5.0 - June 2016</option>
<option value="4_2_release_notes.html">Version 4.2 - December 2014</option>
<option value="4_1_release_notes.html">Version 4.1 - April 2014</option>
<option value="4_0_release_notes.html">Version 4.0 - June 2013</option>
<option value="3_2_release_notes.html">Version 3.2 - January 2012</option>
<option value="3_1_release_notes.html">Version 3.1 - August 2011</option>
<option value="3_0_release_notes.html">Version 3.0 - August 2010</option>
<option value="2_3_release_notes.html">Version 2.3 - March 2009</option>
<option value="2_2_release_notes.html">Version 2.2 - November 2008</option>
</optgroup>
</select>
</li>
</ul>
</div>
</div>
<hr class="hide" />
<div id="feature">
<div class="wrapper">
<h2>Active Record and PostgreSQL</h2><p>This guide covers PostgreSQL specific usage of Active Record.</p><p>After reading this guide, you will know:</p>
<ul>
<li>How to use PostgreSQL's datatypes.</li>
<li>How to use UUID primary keys.</li>
<li>How to implement full text search with PostgreSQL.</li>
<li>How to back your Active Record models with database views.</li>
</ul>
<div id="subCol">
<h3 class="chapter"><img src="images/chapters_icon.gif" alt="" />Chapters</h3>
<ol class="chapters">
<li>
<a href="#datatypes">Datatypes</a>
<ul>
<li><a href="#bytea">Bytea</a></li>
<li><a href="#array">Array</a></li>
<li><a href="#hstore">Hstore</a></li>
<li><a href="#json-and-jsonb">JSON and JSONB</a></li>
<li><a href="#range-types">Range Types</a></li>
<li><a href="#composite-types">Composite Types</a></li>
<li><a href="#enumerated-types">Enumerated Types</a></li>
<li><a href="#uuid">UUID</a></li>
<li><a href="#bit-string-types">Bit String Types</a></li>
<li><a href="#network-address-types">Network Address Types</a></li>
<li><a href="#geometric-types">Geometric Types</a></li>
</ul>
</li>
<li><a href="#uuid-primary-keys">UUID Primary Keys</a></li>
<li><a href="#full-text-search">Full Text Search</a></li>
<li><a href="#database-views">Database Views</a></li>
</ol>
</div>
</div>
</div>
<div id="container">
<div class="wrapper">
<div id="mainCol">
<p>In order to use the PostgreSQL adapter you need to have at least version 9.3
installed. Older versions are not supported.</p><p>To get started with PostgreSQL have a look at the
<a href="configuring.html#configuring-a-postgresql-database">configuring Rails guide</a>.
It describes how to properly setup Active Record for PostgreSQL.</p><h3 id="datatypes"><a class="anchorlink" href="#datatypes">1 Datatypes</a></h3><p>PostgreSQL offers a number of specific datatypes. Following is a list of types,
that are supported by the PostgreSQL adapter.</p><h4 id="bytea"><a class="anchorlink" href="#bytea">1.1 Bytea</a></h4>
<ul>
<li><a href="https://www.postgresql.org/docs/current/static/datatype-binary.html">type definition</a></li>
<li><a href="https://www.postgresql.org/docs/current/static/functions-binarystring.html">functions and operators</a></li>
</ul>
<div class="code_container">
<pre class="brush: ruby; gutter: false; toolbar: false">
# 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
</pre>
</div>
<h4 id="array"><a class="anchorlink" href="#array">1.2 Array</a></h4>
<ul>
<li><a href="https://www.postgresql.org/docs/current/static/arrays.html">type definition</a></li>
<li><a href="https://www.postgresql.org/docs/current/static/functions-array.html">functions and operators</a></li>
</ul>
<div class="code_container">
<pre class="brush: ruby; gutter: false; toolbar: false">
# 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")
</pre>
</div>
<h4 id="hstore"><a class="anchorlink" href="#hstore">1.3 Hstore</a></h4>
<ul>
<li><a href="https://www.postgresql.org/docs/current/static/hstore.html">type definition</a></li>
<li><a href="https://www.postgresql.org/docs/current/static/hstore.html#id-1.11.7.26.5">functions and operators</a></li>
</ul>
<div class="note"><p>You need to enable the <code>hstore</code> extension to use hstore.</p></div><div class="code_container">
<pre class="brush: ruby; gutter: false; toolbar: false">
# 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"}>]>
</pre>
</div>
<h4 id="json-and-jsonb"><a class="anchorlink" href="#json-and-jsonb">1.4 JSON and JSONB</a></h4>
<ul>
<li><a href="https://www.postgresql.org/docs/current/static/datatype-json.html">type definition</a></li>
<li><a href="https://www.postgresql.org/docs/current/static/functions-json.html">functions and operators</a></li>
</ul>
<div class="code_container">
<pre class="brush: ruby; gutter: false; toolbar: false">
# 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")
</pre>
</div>
<h4 id="range-types"><a class="anchorlink" href="#range-types">1.5 Range Types</a></h4>
<ul>
<li><a href="https://www.postgresql.org/docs/current/static/rangetypes.html">type definition</a></li>
<li><a href="https://www.postgresql.org/docs/current/static/functions-range.html">functions and operators</a></li>
</ul>
<p>This type is mapped to Ruby <a href="http://www.ruby-doc.org/core-2.2.2/Range.html"><code>Range</code></a> objects.</p><div class="code_container">
<pre class="brush: ruby; gutter: false; toolbar: false">
# 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
</pre>
</div>
<h4 id="composite-types"><a class="anchorlink" href="#composite-types">1.6 Composite Types</a></h4>
<ul>
<li><a href="https://www.postgresql.org/docs/current/static/rowtypes.html">type definition</a></li>
</ul>
<p>Currently there is no special support for composite types. They are mapped to
normal text columns:</p><div class="code_container">
<pre class="brush: sql; gutter: false; toolbar: false">
CREATE TYPE full_address AS
(
city VARCHAR(90),
street VARCHAR(90)
);
</pre>
</div>
<div class="code_container">
<pre class="brush: ruby; gutter: false; toolbar: false">
# 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!
</pre>
</div>
<h4 id="enumerated-types"><a class="anchorlink" href="#enumerated-types">1.7 Enumerated Types</a></h4>
<ul>
<li><a href="https://www.postgresql.org/docs/current/static/datatype-enum.html">type definition</a></li>
</ul>
<p>Currently there is no special support for enumerated types. They are mapped as
normal text columns:</p><div class="code_container">
<pre class="brush: ruby; gutter: false; toolbar: false">
# 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!
</pre>
</div>
<p>To add a new value before/after existing one you should use <a href="https://www.postgresql.org/docs/current/static/sql-altertype.html">ALTER TYPE</a>:</p><div class="code_container">
<pre class="brush: ruby; gutter: false; toolbar: false">
# 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
</pre>
</div>
<div class="note"><p>ENUM values can't be dropped currently. You can read why <a href="https://www.postgresql.org/message-id/29F36C7C98AB09499B1A209D48EAA615B7653DBC8A@mail2a.alliedtesting.com">here</a>.</p></div><p>Hint: to show all the values of the all enums you have, you should call this query in <code>rails db</code> or <code>psql</code> console:</p><div class="code_container">
<pre class="brush: sql; gutter: false; toolbar: false">
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
</pre>
</div>
<h4 id="uuid"><a class="anchorlink" href="#uuid">1.8 UUID</a></h4>
<ul>
<li><a href="https://www.postgresql.org/docs/current/static/datatype-uuid.html">type definition</a></li>
<li><a href="https://www.postgresql.org/docs/current/static/pgcrypto.html#id-1.11.7.35.7">pgcrypto generator function</a></li>
<li><a href="https://www.postgresql.org/docs/current/static/uuid-ossp.html">uuid-ossp generator functions</a></li>
</ul>
<div class="note"><p>You need to enable the <code>pgcrypto</code> (only PostgreSQL >= 9.4) or <code>uuid-ossp</code>
extension to use uuid.</p></div><div class="code_container">
<pre class="brush: ruby; gutter: false; toolbar: false">
# 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"
</pre>
</div>
<p>You can use <code>uuid</code> type to define references in migrations:</p><div class="code_container">
<pre class="brush: ruby; gutter: false; toolbar: false">
# 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
</pre>
</div>
<p>See <a href="#uuid-primary-keys">this section</a> for more details on using UUIDs as primary key.</p><h4 id="bit-string-types"><a class="anchorlink" href="#bit-string-types">1.9 Bit String Types</a></h4>
<ul>
<li><a href="https://www.postgresql.org/docs/current/static/datatype-bit.html">type definition</a></li>
<li><a href="https://www.postgresql.org/docs/current/static/functions-bitstring.html">functions and operators</a></li>
</ul>
<div class="code_container">
<pre class="brush: ruby; gutter: false; toolbar: false">
# 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!
</pre>
</div>
<h4 id="network-address-types"><a class="anchorlink" href="#network-address-types">1.10 Network Address Types</a></h4>
<ul>
<li><a href="https://www.postgresql.org/docs/current/static/datatype-net-types.html">type definition</a></li>
</ul>
<p>The types <code>inet</code> and <code>cidr</code> are mapped to Ruby
<a href="http://www.ruby-doc.org/stdlib-2.2.2/libdoc/ipaddr/rdoc/IPAddr.html"><code>IPAddr</code></a>
objects. The <code>macaddr</code> type is mapped to normal text.</p><div class="code_container">
<pre class="brush: ruby; gutter: false; toolbar: false">
# 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"
</pre>
</div>
<h4 id="geometric-types"><a class="anchorlink" href="#geometric-types">1.11 Geometric Types</a></h4>
<ul>
<li><a href="https://www.postgresql.org/docs/current/static/datatype-geometric.html">type definition</a></li>
</ul>
<p>All geometric types, with the exception of <code>points</code> are mapped to normal text.
A point is casted to an array containing <code>x</code> and <code>y</code> coordinates.</p><h3 id="uuid-primary-keys"><a class="anchorlink" href="#uuid-primary-keys">2 UUID Primary Keys</a></h3><div class="note"><p>You need to enable the <code>pgcrypto</code> (only PostgreSQL >= 9.4) or <code>uuid-ossp</code>
extension to generate random UUIDs.</p></div><div class="code_container">
<pre class="brush: ruby; gutter: false; toolbar: false">
# 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"
</pre>
</div>
<div class="note"><p><code>gen_random_uuid()</code> (from <code>pgcrypto</code>) is assumed if no <code>:default</code> option was
passed to <code>create_table</code>.</p></div><h3 id="full-text-search"><a class="anchorlink" href="#full-text-search">3 Full Text Search</a></h3><div class="code_container">
<pre class="brush: ruby; gutter: false; toolbar: false">
# 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")
</pre>
</div>
<h3 id="database-views"><a class="anchorlink" href="#database-views">4 Database Views</a></h3>
<ul>
<li><a href="https://www.postgresql.org/docs/current/static/sql-createview.html">view creation</a></li>
</ul>
<p>Imagine you need to work with a legacy database containing the following table:</p><div class="code_container">
<pre class="brush: plain; gutter: false; toolbar: false">
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")
</pre>
</div>
<p>This table does not follow the Rails conventions at all.
Because simple PostgreSQL views are updateable by default,
we can wrap it as follows:</p><div class="code_container">
<pre class="brush: ruby; gutter: false; toolbar: false">
# 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
</pre>
</div>
<div class="note"><p>This application only cares about non-archived <code>Articles</code>. A view also
allows for conditions so we can exclude the archived <code>Articles</code> directly.</p></div>
<h3>Feedback</h3>
<p>
You're encouraged to help improve the quality of this guide.
</p>
<p>
Please contribute if you see any typos or factual errors.
To get started, you can read our <a href="https://edgeguides.rubyonrails.org/contributing_to_ruby_on_rails.html#contributing-to-the-rails-documentation">documentation contributions</a> section.
</p>
<p>
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
<a href="https://edgeguides.rubyonrails.org">Edge Guides</a> first to verify
if the issues are already fixed or not on the master branch.
Check the <a href="ruby_on_rails_guides_guidelines.html">Ruby on Rails Guides Guidelines</a>
for style and conventions.
</p>
<p>
If for whatever reason you spot something to fix but cannot patch it yourself, please
<a href="https://github.com/rails/rails/issues">open an issue</a>.
</p>
<p>And last but not least, any kind of discussion regarding Ruby on Rails
documentation is very welcome on the <a href="https://groups.google.com/forum/#!forum/rubyonrails-docs">rubyonrails-docs mailing list</a>.
</p>
</div>
</div>
</div>
<hr class="hide" />
<div id="footer">
<div class="wrapper">
<p>This work is licensed under a <a href="https://creativecommons.org/licenses/by-sa/4.0/">Creative Commons Attribution-ShareAlike 4.0 International</a> License</p>
<p>"Rails", "Ruby on Rails", and the Rails logo are trademarks of David Heinemeier Hansson. All rights reserved.</p>
</div>
</div>
</body>
</html>