Adding custom data types to your Postgres database in Rails

This week I learned from a colleague that PostgreSQL allows you to create custom domains, which it describes as the following:

a data type with optional constraints (restrictions on the allowed set of values)

Creating the custom domain in Rails

For our current client, a 15-digit numeric string is the protocol for identifying resources in their industry. In our Rails migration for creating this domain, we used something like this:

def change
  execute <<~SQL
    CREATE DOMAIN pid_type AS VARCHAR(15) CHECK(VALUE ~ '^\\d{15}$');
  SQL
end

Here we're defining a custom domain pid_type that can be used to store 15-digit numeric strings in our database that will we reject any nonconforming values.

Adding columns that use our custom domain

After running this, we can now add columns with a type of "pid_type". For example, let's say we needed to add this column to our notes table.

def change
  add_column :notes, :pid, "pid_type"
end

Why not just use an ActiveRecord validation?

Before we added this custom domain, we used a concern to share an ActiveRecord validation that was similiar to our database check constraint (checking that the value was a 15-digit numeric string).

module Pidable
   extend ActiveSupport::Concern

   included do
     validates :pid, length: {is: 15}
     validates :pid, numericality: {only_integer: true}
   end
 end

However, since these records are stricly created via ETL processes (data is being imported from other systems), we realized we did not need to present error information to a user at any point. Also, the database constraint gives us safety at the database layer in case we ever need to bypass ActiveRecord when inserting records.

Conclusion

That just about wraps it up. I don't think this is a feature I will reach for often, but Postgres continues to astound me. What a wonderful foundation on which to build your application. 🐘 == 💯