Martian Open Source
This talk is about…
Subtle differences
Example of subtle yet important difference
Are they same?
…
Left: road sign conforming Vienna Convention on Road Signs and Signals
Right: Japanese road sign per “Order on Road Sign, Road Line, and Road Surface Marking”
Example of subtle yet important difference
Are they same?
NO!
Both require drivers to give a way…
European sign doesn’t have a requirement to stop or even slow down!
Example of subtle yet important difference
Are they same?
YES!
Stop signs around the world are mostly octagonal.
Japanese signs have “STOP” word in English after 2017, but are still pretty rare.
And how it relates to Ruby and PostgreSQL?
And how it relates to Ruby and PostgreSQL?
Application data Data in the database
Ruby vs PostgreSQL data types
Are they same?
Integers
Integer
Variable length
integer
2, 4, 8 bytes signed
1.size # => 8 (bytes) (256**8 - 1).size # => 8 (bytes) (256**8).size # => 9 (bytes) (256**40 - 1).size # => 40 (bytes)1.size # => 8 (bytes) (256**8 - 1).size # => 8 (bytes) (256**8).size # => 9 (bytes) (256**40 - 1).size # => 40 (bytes)
In ActiveModel there is validation for databases:
Test.create(value: 2147483648) # ActiveModel::RangeError: 2147483648 is out of range # for ActiveModel::Type::Integer with limit 4 bytesTest.create(value: 2147483648) # ActiveModel::RangeError: 2147483648 is out of range # for ActiveModel::Type::Integer with limit 4 bytes
| Name | Size | Range |
|---|---|---|
smallint | 2 | -32768 to +32767 |
integer | 4 | -2147483648 to +2147483647 |
bigint | 8 | -9223372036854775808 to +9223372036854775807 |
INSERT INTO "tests" ("value") VALUES (2147483648); -- ERROR: integer out of rangeINSERT INTO "tests" ("value") VALUES (2147483648); -- ERROR: integer out of range
See bignum.c in Ruby sources.
Oh no, I have integer primary keys! What to do?
Don’t panic!
Use pghero or Heroku pg:diagnose to detect problematic primary keys.
Migrate to
bigintoruuidif needed (use triggers, Luke!)In case of emergency, remember that all integers are signed!
You always have 2 more billions of values on the
darknegative side!ALTER SEQUENCE tablename_id_seq MINVALUE -2147483647 RESTART WITH -2147483647;ALTER SEQUENCE tablename_id_seq MINVALUE -2147483647 RESTART WITH -2147483647;
Example of pkey migration from int to bigint: engineering.silverfin.com/pg-zero-downtime-bigint-migration
Floating point numbers
IEEE 754
Float
8 bytes (double-precision)
real — 4 bytes
double — 8 bytes
0.1 + 0.2 # => 0.30000000000000004 Float::MAX # => 1.7976931348623157e+308 Float::MAX + '1e+308'.to_f # => Infinity # BUT! Float::MAX + '0.1'.to_f # => 1.7976931348623157e+308 🤔 Float::MAX == (Float::MAX + '0.1'.to_f) # => true 🤯 Float::NAN == Float::NAN # => false0.1 + 0.2 # => 0.30000000000000004 Float::MAX # => 1.7976931348623157e+308 Float::MAX + '1e+308'.to_f # => Infinity # BUT! Float::MAX + '0.1'.to_f # => 1.7976931348623157e+308 🤔 Float::MAX == (Float::MAX + '0.1'.to_f) # => true 🤯 Float::NAN == Float::NAN # => false
SELECT 0.1::float + 0.2::float; -- 0.30000000000000004 SELECT 0.1 + 0.2; -- 0.3 (but it is NOT float!) SELECT '1.7976931348623157e+308'::float + '1e+308'::float; --- ERROR: value out of range: overflow SELECT '+inf'::double precision; -- Infinity 🤷 SELECT '1.7976931348623157e+308'::float = ('1.7976931348623157e+308'::float + '0.1'::float); -- true ¯\_(ツ)_/¯ SELECT 'NaN'::float = 'NaN'::float; -- true 🤯SELECT 0.1::float + 0.2::float; -- 0.30000000000000004 SELECT 0.1 + 0.2; -- 0.3 (but it is NOT float!) SELECT '1.7976931348623157e+308'::float + '1e+308'::float; --- ERROR: value out of range: overflow SELECT '+inf'::double precision; -- Infinity 🤷 SELECT '1.7976931348623157e+308'::float = ('1.7976931348623157e+308'::float + '0.1'::float); -- true ¯\_(ツ)_/¯ SELECT 'NaN'::float = 'NaN'::float; -- true 🤯
See Ruby docs for Float
More fun at 0.30000000000000004.com!
Don’t use floats for calculating money!
Never ever!
Arbitrary precision numbers
BigDecimal
Variable length
numeric
Variable length
BigDecimal("0.1") + BigDecimal("0.2") # => 0.3e0 BigDecimal("NaN") == BigDecimal("NaN") # => false BigDecimal("1.0") / BigDecimal("0.0") #=> Infinity # To match PostgreSQL behavior: BigDecimal.mode(BigDecimal::EXCEPTION_OVERFLOW, true) BigDecimal("1.0") / BigDecimal("0.0") # Computation results in 'Infinity' (FloatDomainError) BigDecimal("0.1") + 0.2.to_d == 0.30000000000000004 # true 🤔BigDecimal("0.1") + BigDecimal("0.2") # => 0.3e0 BigDecimal("NaN") == BigDecimal("NaN") # => false BigDecimal("1.0") / BigDecimal("0.0") #=> Infinity # To match PostgreSQL behavior: BigDecimal.mode(BigDecimal::EXCEPTION_OVERFLOW, true) BigDecimal("1.0") / BigDecimal("0.0") # Computation results in 'Infinity' (FloatDomainError) BigDecimal("0.1") + 0.2.to_d == 0.30000000000000004 # true 🤔
SELECT 0.1 + 0.2; -- 0.3 which is decimal SELECT 'NaN'::decimal = 'NaN'::decimal; -- true SELECT '1.0'::decimal / '0.0'::decimal; -- ERROR: division by zero SELECT (0.1 + 0.2) = (0.1::float + 0.2::float); -- falseSELECT 0.1 + 0.2; -- 0.3 which is decimal SELECT 'NaN'::decimal = 'NaN'::decimal; -- true SELECT '1.0'::decimal / '0.0'::decimal; -- ERROR: division by zero SELECT (0.1 + 0.2) = (0.1::float + 0.2::float); -- false
See Ruby docs for BigDecimal
Use numeric to store money!
But there is money type, isn’t it?
BigDecimal
Variable size
money
8 byte fixed-precision number.
# If the database locale setting isn't `en_US`: # Creation may fail: Product.create!(price: 100500.42) # ERROR: invalid input syntax for type money: "100500.42" # Or it can succeed, but won't be able to be parsed back: Product.last.price # => 0.0# If the database locale setting isn't `en_US`: # Creation may fail: Product.create!(price: 100500.42) # ERROR: invalid input syntax for type money: "100500.42" # Or it can succeed, but won't be able to be parsed back: Product.last.price # => 0.0
-- on production: SELECT 100500.42::money; -- $100,500.42 -- on dev machine: SELECT 100500.42::money; -- ¥ 100,500 -- 🤯 But it should be dollars, and where are my cents?-- on production: SELECT 100500.42::money; -- $100,500.42 -- on dev machine: SELECT 100500.42::money; -- ¥ 100,500 -- 🤯 But it should be dollars, and where are my cents?
ActiveRecord has to parse textual representation, see connection_adapters/postgresql/oid/money.rb
Also see issue № 31457 for lots of pain.
Both output and acceptable input format depends on session-level lc_monetary setting!
Precision is defined by lc_monetary at database creation time and can’t be changed!
Strings and texts, lyrics and prose
String
Variable size
varchar, text
variable size, max 1 GB
"こんにちは地球人！".encoding # => #<Encoding:UTF-8> "\xe3\x2e\x2e".encoding # => #<Encoding:UTF-8> "\xe3\x2e\x2e".valid_encoding? # => false "これ\x00やばい!".valid_encoding? # => true"こんにちは地球人！".encoding # => #<Encoding:UTF-8> "\xe3\x2e\x2e".encoding # => #<Encoding:UTF-8> "\xe3\x2e\x2e".valid_encoding? # => false "これ\x00やばい!".valid_encoding? # => true
SELECT 'こんにちは地球人！'; -- こんにちは地球人！ SELECT E'\xe3\x2e\x2e'); -- ERROR: invalid byte sequence for encoding "UTF8": 0xe3 0x2e 0x2e SELECT E'これ\x00やばい!'; -- ERROR: invalid byte sequence for encoding "UTF8": 0x00SELECT 'こんにちは地球人！'; -- こんにちは地球人！ SELECT E'\xe3\x2e\x2e'); -- ERROR: invalid byte sequence for encoding "UTF8": 0xe3 0x2e 0x2e SELECT E'これ\x00やばい!'; -- ERROR: invalid byte sequence for encoding "UTF8": 0x00
Read the docs: String
Read the docs: 8.3. Character Types
So many string types!
Don’t use
char(n)It is always size of
nand stores unnecessary spaces at right. Mostly for compatibility with older applications.varcharandtextare effectively the same, choose whatever you like.stringin migrations isvarcharin PostgreSQL.
💡 Did you know?
SimpleForm gem will render multi-line HTML <textarea> tag for text type and single-line <input type="text"> for character varying.
Convention over configuration!
See SimpleForm gem README: github.com/heartcombo/simple_form
Please, use utf8everywhere.org!
Binary data
String
Variable size
bytea
Variable size, max 1 GB
data = File.binread(“meme.png”) # => "\x89PNG\r\n\x1A…" data.encoding # => #<Encoding:ASCII-8BIT> data.bytesize # => 46534 Test.last.blob # => "\x89PNG\r\n\x1A…" Test.last.blob_before_type_cast.bytesize # => 46534 Test.last.blob_before_type_cast # => "\\x89504e470d0a1a0a" Test.last.blob_before_type_cast.bytesize # => 93070data = File.binread(“meme.png”) # => "\x89PNG\r\n\x1A…" data.encoding # => #<Encoding:ASCII-8BIT> data.bytesize # => 46534 Test.last.blob # => "\x89PNG\r\n\x1A…" Test.last.blob_before_type_cast.bytesize # => 46534 Test.last.blob_before_type_cast # => "\\x89504e470d0a1a0a" Test.last.blob_before_type_cast.bytesize # => 93070
SELECT '\x89504e470d0a1a0a…'::bytea; # Note hexadecimal format ↑SELECT '\x89504e470d0a1a0a…'::bytea; # Note hexadecimal format ↑
Memory and network traffic consumption: 📈
See Binary Data Types page in the docs.
What if 1 GB isn’t enough?
You can’t store more in a table column (hard limit)
But you can store up 4 TB in large objects table!
And there is a gem for that:
More about it in PG docs: 70.2. TOAST
Dates
Date
date
4 bytes
Internally stores number of days since year 4713 BC up to infinity.
The Julian day number is in elapsed days since noon (Greenwich Mean Time) on January 1, 4713 BCE (in the Julian calendar).
The day count is virtually the astronomical Julian day number.
Internally stores number of days since year 4713 BC up to year 5874897 AD.
In the Julian Date system, each day has a sequential number, starting from JD 0 (which is sometimes called the Julian Date). JD 0 corresponds to 1 January 4713 BC in the Julian calendar.
See the docs for Date class.
See B.7. Julian Dates in PostgreSQL docs.
Time and timezones
Time
AS::TimeWithZone
Two UNIX timestamps inside and tzdata also
timestamp
timestamptz
8 bytes, microsecond precision
Time.now # => 2022-10-22 13:42:42 +0900 Time.current # => Sat, 22 Oct 2022 04:42:42 UTC +00:00 Time.current.time_zone # => #<ActiveSupport::TimeZone name="UTC", @tzinfo=#<TZInfo::DataTimezone: Etc/UTC>> Time.use_zone("Asia/Tokyo") { Time.current } # => Sat, 22 Oct 2020 13:42:42 JST +09:00Time.now # => 2022-10-22 13:42:42 +0900 Time.current # => Sat, 22 Oct 2022 04:42:42 UTC +00:00 Time.current.time_zone # => #<ActiveSupport::TimeZone name="UTC", @tzinfo=#<TZInfo::DataTimezone: Etc/UTC>> Time.use_zone("Asia/Tokyo") { Time.current } # => Sat, 22 Oct 2020 13:42:42 JST +09:00
CREATE TABLE tests (t1 timestamp, t2 timestamptz); SET SESSION timezone TO 'Etc/UTC'; INSERT INTO tests (t1, t2) VALUES (now(), now()); SET SESSION timezone TO 'Asia/Tokyo'; INSERT INTO tests (t1, t2) VALUES (now(), now()); SET SESSION timezone TO 'Europe/Lisbon'; INSERT INTO tests (t1, t2) VALUES (now(), now()); SET SESSION timezone TO 'Asia/Tokyo'; SELECT * FROM tests; t1 | t2 ---------------------+-------------------------------- 2022-10-22 04:42:42 │ 2022-10-02 13:42:42+09 │ 2022-10-22 13:42:42 │ 2022-10-02 13:42:42+09 │ 2022-10-22 05:42:42 │ 2022-10-02 13:42:42+09 │CREATE TABLE tests (t1 timestamp, t2 timestamptz); SET SESSION timezone TO 'Etc/UTC'; INSERT INTO tests (t1, t2) VALUES (now(), now()); SET SESSION timezone TO 'Asia/Tokyo'; INSERT INTO tests (t1, t2) VALUES (now(), now()); SET SESSION timezone TO 'Europe/Lisbon'; INSERT INTO tests (t1, t2) VALUES (now(), now()); SET SESSION timezone TO 'Asia/Tokyo'; SELECT * FROM tests; t1 | t2 ---------------------+-------------------------------- 2022-10-22 04:42:42 │ 2022-10-02 13:42:42+09 │ 2022-10-22 13:42:42 │ 2022-10-02 13:42:42+09 │ 2022-10-22 05:42:42 │ 2022-10-02 13:42:42+09 │
Ruby on Rails uses UTC timezone internally.
Use timestamp with time zone whenever possible!
How to not mess up with timezones
Use timezone-aware methods
Use
Time.currentandDate.currentinstead ofTime.nowandDate.todayConvert timestamps to user time zone
Time.use_zone(user.timezone) do # Do SQL queries, render views, … end # or Time.current.in_time_zone(user.timezone)Time.use_zone(user.timezone) do # Do SQL queries, render views, … end # or Time.current.in_time_zone(user.timezone)Don’t use dates in SQL, use timestamps
- Posts.where(published_at: Date.today...Date.tomorrow) + Posts.where(published_at: Time.current.beginning_of_day..Time.current.end_of_day)- Posts.where(published_at: Date.today...Date.tomorrow) + Posts.where(published_at: Time.current.beginning_of_day..Time.current.end_of_day)
More tips here: thoughtbot.com/blog/its-about-time-zones
Void and uncertainity
NilClass
NULL
nil == nil # => true ¯\_(ツ)_/¯nil == nil # => true ¯\_(ツ)_/¯
SELECT NULL = NULL; -- NULL 🚨 SELECT NULL IS NULL; -- true SELECT NULL IS DISTINCT FROM NULL; -- false SELECT 'Ruby' = NULL; -- NULL 🚨 SELECT 'Ruby' IS NULL; -- false SELECT 'Ruby' IS DISTINCT FROM NULL; -- trueSELECT NULL = NULL; -- NULL 🚨 SELECT NULL IS NULL; -- true SELECT NULL IS DISTINCT FROM NULL; -- false SELECT 'Ruby' = NULL; -- NULL 🚨 SELECT 'Ruby' IS NULL; -- false SELECT 'Ruby' IS DISTINCT FROM NULL; -- true
PostgreSQL-specific datatypes
JSON
Hash, Array
json, jsonb
Variable length, up to 1GB
Be careful with symbols as keys
{ "foo" => "bar", foo: "baz" }.to_json # {"foo":"baz"}{ "foo" => "bar", foo: "baz" }.to_json # {"foo":"baz"}
Define as_json method on your classes to serialize them to JSON automatically.
Behavior of JSON.dump and to_json in Rails is different!
JSON saves value as is (it is just a string)
SELECT '{"foo": "bar","foo":"baz"}'::json; -- {"foo": "bar","foo":"baz"}SELECT '{"foo": "bar","foo":"baz"}'::json; -- {"foo": "bar","foo":"baz"}
JSONB is effective but strict: no duplicate keys, doesn’t preserve whitespaces, etc…
SELECT '{"foo": "bar","foo":"baz"}'::jsonb; -- {"foo": "baz"}SELECT '{"foo": "bar","foo":"baz"}'::jsonb; -- {"foo": "baz"}
Inside: string (no null-bytes!), numeric, …
See ActiveSupport’s core_ext/object/json.rb
JSON on steroids
Use store_model gem to make powerful value objects from JSON fields.
But don’t overuse!
There is performance penalty for serialization and deserialization.
Ranges
Range
intrange, tsrange, …
intmultirange, …
5..7 or 5...8 Time.current..1.day.from_now # endless or beginless ranges Time.current.. ..Date.yesterday nil.. or Range.new(nil, nil) # Beginning is always included if possible :-( Test.pluck("intrange(1, 5, '()')").first # 2...5 Test.pluck("tstzrange(now(), now() + '1 hour', '()')").first # ArgumentError: The Ruby Range object does not # support excluding the beginning of a Range.5..7 or 5...8 Time.current..1.day.from_now # endless or beginless ranges Time.current.. ..Date.yesterday nil.. or Range.new(nil, nil) # Beginning is always included if possible :-( Test.pluck("intrange(1, 5, '()')").first # 2...5 Test.pluck("tstzrange(now(), now() + '1 hour', '()')").first # ArgumentError: The Ruby Range object does not # support excluding the beginning of a Range.
SELECT int8range(5, 7, '[]'); -- [5,8] SELECT int8range(5, 8); -- [5,8) SELECT tstzrange(now(), now() + '1 day', '()'); -- ["2022-10-22 14:42:42+09","2022-10-23 14:42:42+09") -- endless or beginless ranges SELECT tstzrange(now(), NULL); SELECT tstzrange(NULL, NULL); -- PG 14: Multiranges and operators SELECT nummultirange(numrange(1, 20)) - nummultirange(numrange(4, 6)); -- {[1,4),[6,20)} -- and many more… (exclusion constraints!)SELECT int8range(5, 7, '[]'); -- [5,8] SELECT int8range(5, 8); -- [5,8) SELECT tstzrange(now(), now() + '1 day', '()'); -- ["2022-10-22 14:42:42+09","2022-10-23 14:42:42+09") -- endless or beginless ranges SELECT tstzrange(now(), NULL); SELECT tstzrange(NULL, NULL); -- PG 14: Multiranges and operators SELECT nummultirange(numrange(1, 20)) - nummultirange(numrange(4, 6)); -- {[1,4),[6,20)} -- and many more… (exclusion constraints!)
Additional methods in the facets gem.
UUID
Also take a look at upcoming UUIDv6, v7, and v8!
datatracker.ietf.org/doc/html/draft-peabody-dispatch-new-uuid-format-04
String
36 bytes
uuid
16 bytes
# All-random UUIDv4 SecureRandom.uuid # => “40f15398-4b38-4e16-8b3c-ff16fc960d38” # Determined UUIDv5 (hash-based) Digest::UUID.uuid_v5(Digest::UUID::DNS_NAMESPACE, "name") # => "9b8edca0-90f2-5031-8e5d-3f708834696c"# All-random UUIDv4 SecureRandom.uuid # => “40f15398-4b38-4e16-8b3c-ff16fc960d38” # Determined UUIDv5 (hash-based) Digest::UUID.uuid_v5(Digest::UUID::DNS_NAMESPACE, "name") # => "9b8edca0-90f2-5031-8e5d-3f708834696c"
CREATE EXTENSION "pgcrypto"; SELECT gen_random_uuid(); -- 2cfff962-4a24-4ef3-b2f8-35351b18bf63 CREATE EXTENSION "uuid-ossp"; SELECT uuid_generate_v5(uuid_ns_dns(), 'name'); -- 9b8edca0-90f2-5031-8e5d-3f708834696cCREATE EXTENSION "pgcrypto"; SELECT gen_random_uuid(); -- 2cfff962-4a24-4ef3-b2f8-35351b18bf63 CREATE EXTENSION "uuid-ossp"; SELECT uuid_generate_v5(uuid_ns_dns(), 'name'); -- 9b8edca0-90f2-5031-8e5d-3f708834696c
See Rails docs for Digest::UUID
IP addresses
IPAddr
inet, cidr
7 or 19 bytes both
ip6 = IPAddr.new "3ffe:505:2::1" ip4 = IPAddr.new "192.168.2.0/24" IPAddr.new("192.168.2.0/24").mask(16) #<IPAddr: IPv4:192.168.0.0/255.255.0.0>ip6 = IPAddr.new "3ffe:505:2::1" ip4 = IPAddr.new "192.168.2.0/24" IPAddr.new("192.168.2.0/24").mask(16) #<IPAddr: IPv4:192.168.0.0/255.255.0.0>
SELECT '::1'::inet; SELECT '127.0.0.1/32'::inet; SELECT set_masklen(cidr '192.168.1.0/24', 16); -- 192.168.0.0/16 SELECT inet_merge(inet '192.168.1.5/24', inet '192.168.2.5/24'); -- 192.168.0.0/22SELECT '::1'::inet; SELECT '127.0.0.1/32'::inet; SELECT set_masklen(cidr '192.168.1.0/24', 16); -- 192.168.0.0/16 SELECT inet_merge(inet '192.168.1.5/24', inet '192.168.2.5/24'); -- 192.168.0.0/22
inet works with both host and network addresses.
cidr works with network addresses only.
See IPAddr docs.
Durations
ActiveSupport::Duration
interval
16 bytes
Time.current + 1.year # => Thu, 18 Jun 2021 21:00:00 MSK +03:00 100500.weeks.iso8601 # => "P100500W" 1.month.to_i # => 2629746 (30.436875 days in seconds)Time.current + 1.year # => Thu, 18 Jun 2021 21:00:00 MSK +03:00 100500.weeks.iso8601 # => "P100500W" 1.month.to_i # => 2629746 (30.436875 days in seconds)
SELECT now() + ‘1 year’; -- 2021-06-18 21:00:00+03 SELECT '100500 weeks'::interval; -- 703500 days SELECT EXTRACT(epoch FROM '1 month'::interval); -- 2592000 (30.0 days in seconds)SELECT now() + ‘1 year’; -- 2021-06-18 21:00:00+03 SELECT '100500 weeks'::interval; -- 703500 days SELECT EXTRACT(epoch FROM '1 month'::interval); -- 2592000 (30.0 days in seconds)
Disclaimer: I added it to Rails in pull request № 16919.
Supported out-of-the-box in Ruby on Rails 6.1+
Enums
String
Custom enum types
4 bytes
# In migration (Rails 7+): create_enum :status, ["draft", "published", "archived", "trashed"] change_table :posts do |t| t.enum :status, enum_type: "status", default: "draft", null: false end # In the application code: class Article < ApplicationRecord enum :status, { draft: "draft", published: "published", archived: "archived", trashed: "trashed" } end Article.last.status #=> "draft" Article.last.draft? #=> true Article.last.published! # UPDATE articles SET status = 'published' WHERE id = …# In migration (Rails 7+): create_enum :status, ["draft", "published", "archived", "trashed"] change_table :posts do |t| t.enum :status, enum_type: "status", default: "draft", null: false end # In the application code: class Article < ApplicationRecord enum :status, { draft: "draft", published: "published", archived: "archived", trashed: "trashed" } end Article.last.status #=> "draft" Article.last.draft? #=> true Article.last.published! # UPDATE articles SET status = 'published' WHERE id = …
CREATE TYPE status AS ENUM ('draft', 'published', 'archived', 'trashed'); ALTER TABLE posts ADD COLUMN "status" status NOT NULL DEFAULT 'draft'; INSERT INTO posts (status) VALUES ('published'); INSERT INTO posts (status) VALUES ('draft'); SELECT id, status FROM posts; id | status ----+------------ 2 | draft 1 | publishedCREATE TYPE status AS ENUM ('draft', 'published', 'archived', 'trashed'); ALTER TABLE posts ADD COLUMN "status" status NOT NULL DEFAULT 'draft'; INSERT INTO posts (status) VALUES ('published'); INSERT INTO posts (status) VALUES ('draft'); SELECT id, status FROM posts; id | status ----+------------ 2 | draft 1 | published
Values are human-readable in SQL
On Rails < 7 you can use activerecord-postgres_enum gem
See 8.7 Enumerated Types page in PostgreSQL docs
Inside ActiveRecord
How datatypes are working under the hood
Example of composite type use case
What if we want to:
Store products with prices in different currencies in one table
Work with price and currency as a whole
There is a great money gem in Ruby, but how to do it in SQL?
And do some calculations without having to write complex SQL queries with joins.
Before:
User (string currency) has many Product (numeric price)User (string currency) has many Product (numeric price)
After:
User (string currency) # only as a setting! has many Product (true_money price (string+numeric))User (string currency) # only as a setting! has many Product (true_money price (string+numeric))
Create custom datatype
Declare composite datatype in the database:
CREATE TYPE _true_money AS ( currency varchar, amount numeric ); -- type with constraints to allow: -- - either NULL value (no price, can be forbidden by NOT NULL) -- - or value with both currency and amount specified CREATE DOMAIN true_money AS _true_money CHECK ( value IS NULL AND value IS DISTINCT FROM (null, null)::_true_money OR ((value).currency IS NOT NULL AND (value).amount IS NOT NULL) );CREATE TYPE _true_money AS ( currency varchar, amount numeric ); -- type with constraints to allow: -- - either NULL value (no price, can be forbidden by NOT NULL) -- - or value with both currency and amount specified CREATE DOMAIN true_money AS _true_money CHECK ( value IS NULL AND value IS DISTINCT FROM (null, null)::_true_money OR ((value).currency IS NOT NULL AND (value).amount IS NOT NULL) );
Fun fact about composite datatypes
Every table defines own datatype which can be used elsewhere
CREATE TABLE "inner" ( v1 integer, v2 text ); CREATE TABLE "outer" (v inner); INSERT INTO "outer" (v) VALUES ((42,'Hello world!')); SELECT * FROM "outer"; v ------------------- (42,"Hello world!")CREATE TABLE "inner" ( v1 integer, v2 text ); CREATE TABLE "outer" (v inner); INSERT INTO "outer" (v) VALUES ((42,'Hello world!')); SELECT * FROM "outer"; v ------------------- (42,"Hello world!")
But don’t use it in reality, please!
(There are limitations)
Use composite datatype
ALTER TABLE tests ADD COLUMN price true_money; INSERT INTO tests (price) VALUES (('JPY',10000.0)); INSERT INTO tests (price) VALUES ('("JPY",100.0)'); SELECT price FROM tests; -- (JPY,10000.0),(JPY,100.0) SELECT (price).currency, (price).amount FROM tests; currency | amount ----------+--------- JPY | 10000.0 JPY | 100.0ALTER TABLE tests ADD COLUMN price true_money; INSERT INTO tests (price) VALUES (('JPY',10000.0)); INSERT INTO tests (price) VALUES ('("JPY",100.0)'); SELECT price FROM tests; -- (JPY,10000.0),(JPY,100.0) SELECT (price).currency, (price).amount FROM tests; currency | amount ----------+--------- JPY | 10000.0 JPY | 100.0
See 8.16. Composite Types in PostgreSQL docs for more advices and caveats.
Declare it in ActiveRecord
module ActiveRecord module ConnectionAdapters module PostgreSQL module OID class TrueMoney < Type::Value def type :true_money end # Here will be (de)serialization code end end end end endmodule ActiveRecord module ConnectionAdapters module PostgreSQL module OID class TrueMoney < Type::Value def type :true_money end # Here will be (de)serialization code end end end end end
Deserialization
def deserialize(value) return nil if value.nil? currency, amount = value.match(/\A\("?(\w+)"?,(\d+(?:\.\d+)?)\)\z/).captures ::Money.from_amount(BigDecimal(amount), currency) enddef deserialize(value) return nil if value.nil? currency, amount = value.match(/\A\("?(\w+)"?,(\d+(?:\.\d+)?)\)\z/).captures ::Money.from_amount(BigDecimal(amount), currency) end
And "(USD,4.2)" becomes #<Money fractional:420 currency:USD> in Ruby ✨
Casting user input
Add ability to assign ready object to attribute:
def cast(value) return nil if value.nil? case value when ::Money then value when String then deserialize(value) else raise NotImplementedError, "Don't know how to cast #{value.class} #{value.inspect} into Money" end enddef cast(value) return nil if value.nil? case value when ::Money then value when String then deserialize(value) else raise NotImplementedError, "Don't know how to cast #{value.class} #{value.inspect} into Money" end end
Deserialization and input casting at once
def cast_value(value) case value when ::Money then value when String currency, amount = value.match(/\A\("?(\w+)"?,(\d+(?:\.\d+)?)\)\z/).captures ::Money.from_amount(BigDecimal(amount), currency) else raise NotImplementedError, "Don't know how to cast #{value.class} #{value.inspect} into Money" end end enddef cast_value(value) case value when ::Money then value when String currency, amount = value.match(/\A\("?(\w+)"?,(\d+(?:\.\d+)?)\)\z/).captures ::Money.from_amount(BigDecimal(amount), currency) else raise NotImplementedError, "Don't know how to cast #{value.class} #{value.inspect} into Money" end end end
Replaces both deserialize and cast, also handles nils.
Serialization for the database
def serialize(value) return nil if value.nil? # ActiveRecord will handle NULL for us amount_t = ::ActiveRecord::ConnectionAdapters::PostgreSQLAdapter::OID::Decimal.new currency_t = ::ActiveModel::Type::String.new "(#{currency_t.serialize(value.currency.iso_code).inspect},#{amount_t.serialize(value.amount)})" enddef serialize(value) return nil if value.nil? # ActiveRecord will handle NULL for us amount_t = ::ActiveRecord::ConnectionAdapters::PostgreSQLAdapter::OID::Decimal.new currency_t = ::ActiveModel::Type::String.new "(#{currency_t.serialize(value.currency.iso_code).inspect},#{amount_t.serialize(value.amount)})" end
Reuse available serialization methods for subtypes.
Register datatype in ActiveRecord
PostgreSQLAdapterWithTrueMoney = Module.new do def initialize_type_map(m = type_map) m.register_type "true_money" do |*_args, _sql_type| ::ActiveRecord::ConnectionAdapters::PostgreSQLAdapter::OID::TrueMoney.new end m.alias_type "_true_money", "true_money" super end end ActiveRecord::ConnectionAdapters::PostgreSQLAdapter.prepend(PostgreSQLAdapterWithTrueMoney) ActiveRecord::Type.register( :true_money, ::ActiveRecord::ConnectionAdapters::PostgreSQLAdapter::OID::TrueMoney, adapter: :postgresql, )PostgreSQLAdapterWithTrueMoney = Module.new do def initialize_type_map(m = type_map) m.register_type "true_money" do |*_args, _sql_type| ::ActiveRecord::ConnectionAdapters::PostgreSQLAdapter::OID::TrueMoney.new end m.alias_type "_true_money", "true_money" super end end ActiveRecord::ConnectionAdapters::PostgreSQLAdapter.prepend(PostgreSQLAdapterWithTrueMoney) ActiveRecord::Type.register( :true_money, ::ActiveRecord::ConnectionAdapters::PostgreSQLAdapter::OID::TrueMoney, adapter: :postgresql, )
Also add it for migrations…
module SchemaStatementsWithTrueMoney def type_to_sql(type, limit: nil, precision: nil, scale: nil, array: nil, **) case type.to_s when 'true_money' then 'true_money' else super end end end ActiveRecord::ConnectionAdapters::PostgreSQL::SchemaStatements.prepend(SchemaStatementsWithTrueMoney) module ActiveRecord::ConnectionAdapters::PostgreSQL::ColumnMethods def true_money(name, options = {}) column(name, :true_money, options) end endmodule SchemaStatementsWithTrueMoney def type_to_sql(type, limit: nil, precision: nil, scale: nil, array: nil, **) case type.to_s when 'true_money' then 'true_money' else super end end end ActiveRecord::ConnectionAdapters::PostgreSQL::SchemaStatements.prepend(SchemaStatementsWithTrueMoney) module ActiveRecord::ConnectionAdapters::PostgreSQL::ColumnMethods def true_money(name, options = {}) column(name, :true_money, options) end end
Ready to use!
rails g model Product title price:true_money rails db:migrate rails consolerails g model Product title price:true_money rails db:migrate rails console
Product.create!(title: "Something", price: Money.from_amount(100000, “USD”)) Product.last # => #<Product id: 1, title: "Something", price: 100000.00 USD>Product.create!(title: "Something", price: Money.from_amount(100000, “USD”)) Product.last # => #<Product id: 1, title: "Something", price: 100000.00 USD>
But it is not done yet!
A lot of stuff has to be done to make a full-featured datatype in SQL…
CREATE FUNCTION true_money_add(a true_money, b true_money) RETURNS true_money AS $$ BEGIN IF (a).currency != (b).currency THEN RAISE EXCEPTION '% can not be added to % - currencies does not match', b, a; END IF; RETURN ((a).currency, (a).amount + (b).amount); END; $$ IMMUTABLE RETURNS NULL ON NULL INPUT LANGUAGE plpgsql; CREATE OPERATOR +(leftarg=true_money, rightarg=true_money, procedure=true_money_add);CREATE FUNCTION true_money_add(a true_money, b true_money) RETURNS true_money AS $$ BEGIN IF (a).currency != (b).currency THEN RAISE EXCEPTION '% can not be added to % - currencies does not match', b, a; END IF; RETURN ((a).currency, (a).amount + (b).amount); END; $$ IMMUTABLE RETURNS NULL ON NULL INPUT LANGUAGE plpgsql; CREATE OPERATOR +(leftarg=true_money, rightarg=true_money, procedure=true_money_add);
CREATE FUNCTION true_money_sum(state true_money, value true_money) RETURNS true_money AS $$ BEGIN IF value IS NULL AND state IS NULL THEN RETURN NULL; END IF; IF state IS NULL THEN RETURN value; END IF; RETURN state + value; END; $$ IMMUTABLE LANGUAGE plpgsql; CREATE AGGREGATE sum (true_money) (sfunc = true_money_sum, stype = true_money);CREATE FUNCTION true_money_sum(state true_money, value true_money) RETURNS true_money AS $$ BEGIN IF value IS NULL AND state IS NULL THEN RETURN NULL; END IF; IF state IS NULL THEN RETURN value; END IF; RETURN state + value; END; $$ IMMUTABLE LANGUAGE plpgsql; CREATE AGGREGATE sum (true_money) (sfunc = true_money_sum, stype = true_money);
But then you can do a lot in SQL:
SELECT (price).currency AS currency, sum(price) AS total FROM products GROUP BY currency;SELECT (price).currency AS currency, sum(price) AS total FROM products GROUP BY currency;
After all, one might re-invent abandoned pg-currency
Gems, gems, gems!
Everything That Can Be Invented Has Been Invented
Gems for datatypes
torque-postgresql — standard datatypes not (yet) supported by Rails.
activerecord-postgis-adapter — all the power of PostGIS extension in Ruby.
activerecord-postgres_enum — support enum in migrations and schema (before Rails 7)
Gems for other PostgreSQL features
Because PostgreSQL is much more than datatypes.
- ActiveRecordExtended — functions for datatypes and DSL for queries
- fx — make schema.rb great again with triggers
- scenic — add support for views
- order_query — keyset-pagination for your models
- postgresql_cursor — get more data from the database efficiently
And also martian pg_trunk gem to rule them all get fx, scenic, object dependency management and more within a single gem!
structure.sql no more!
Make schema.rb great again!
That’s it!
Questions?
Thank you!
Our awesome blog: evilmartians.com/chronicles!
(Special thanks to @hachi8833 for translating ≈20 posts to Japanese!)
See these slides at envek.github.io/kaigionrails-postgresql-as-seen-by-rubyists