PostgreSQL in Rails With Active Record

PostgreSql is used as a database in various application and also in rails application. Here some useful data type and it's behavior which are use in rails with PostgreSql.

 JSON Type

         Here we adding a column with json data type and then store the data as json formate on Create


# 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

# The -> operator returns the original JSON type (which might be an object), whereas ->> returns text

Event.where("payload->>'kind' = ?", "user_renamed") 


For more Information:  Json Type Def

Network Address Types

The types inet and cidr are mapped to Ruby IPAddr objects. The macaddr type is mapped to normal text

# 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 < ActiveRecord::Base
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"

Bit String Types

Bit strings are strings of 1's and 0's. They can be used to store or visualize bit masks. There are two SQL bit types: bit(n) and bit varying(n), where n is a positive integer.
(Mask means to block.Masking is the process by which ,only required data is retained and the rest is masked (blocked))
bit type data must match the length n exactly; it is an error to attempt to store shorter or longer bit strings. bit varying data is of variable length up to the maximum length n; longer strings will be rejected. Writing bit without a length is equivalent to bit(1), while bit varying without a length specification means unlimited length.

# 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 # => "01010011"
user.settings = "0xAF"
user.settings # => 10101111
user.save!


For more information: Click

Range Types

Range types are data types representing a range of values of some element type (called the range's subtype). For instance, ranges of timestamp might be used to represent the ranges of time that a meeting room is reserved. In this case the data type is tsrange (short for "timestamp range"), and timestamp is the subtype. The subtype must have a total order so that it is well-defined whether element values are within, before, or after a range of values.
Range types are useful because they represent many element values in a single range value, and because concepts such as overlapping ranges can be expressed clearly. The use of time and date ranges for scheduling purposes is the clearest example; but price ranges, measurement ranges from an instrument, and so forth can also be useful.

This type is mapped to Ruby Range objects.


# 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



Bytea (Binary Type)

A binary string is a sequence of octets (or bytes). Binary strings are distinguished from character strings in two ways. First, binary strings specifically allow storing octets of value zero and other "non-printable" octets (usually, octets outside the range 32 to 126). Character strings disallow zero octets, and also disallow any other octet values and sequences of octet values that are invalid according to the database's selected character set encoding. Second, operations on binary strings process the actual bytes, whereas the processing of character strings depends on locale settings. In short, binary strings are appropriate for storing data that the programmer thinks of as "raw bytes", whereas character strings are appropriate for storing text.
The bytea type supports two external formats for input and output: PostgreSQL's historical "escape" format, and "hex" format. Both of these are always accepted on input. The output format depends on the configuration parameter bytea_output; the default is hex. (Note that the hex format was introduced in PostgreSQL 9.0; earlier versions and some tools don't understand it.)
The SQL standard defines a different binary string type, called BLOB or BINARY LARGE OBJECT. The input format is different from bytea, but the provided functions and operators are mostly the same.


# 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