Rails 7 added support for PostgreSQL generated columns.

To understand what generated columns are, let us take a simple example of orders table:

create_table :orders, force: true do |t|
  t.integer :id
  t.integer :user_id
  t.decimal :price, :decimal, precision: 8, scale: 2
  t.decimal :tax, :decimal, precision: 8, scale: 2
end

When displaying a list orders, we almost always need the total (= price + tax). Also we need to perform some filtering & sorting based on total.

Generated columns were introduced in PostgreSQL 12. These are useful when storing some data which is calculated using other columns.

Rails 5.1.0 added support for such columns in MySQL and MariaDB, but it was not supported by Rails' postgreSQL adapter until now. Rails 7 adds this support for postgreSQL, so we can write:

create_table :orders, force: true do |t|
  t.integer :user_id
  t.decimal :price, precision: 8, scale: 2
  t.decimal :tax, precision: 8, scale: 2
  t.virtual :total, type: :decimal, as: 'price + tax', stored: true
end

And use it as follows:

order = Order.create!(user_id: 1, price: 120, tax: 10)
order.reload
order.total # => 130

Note that the object returned by create! does not have total yet, since it is calculated at database level during insert/update. We can get it by refetching the record.

Database always keeps generated columns up-to-date when any of its constituent columns are changed. One less thing to worry about.

order.update(price: 130)
order.reload
order.total # => 140

They are actual columns in database so we can add indexes on them and also run queries:

Order.where("total >= ?", 100)

This query is much efficient than iterating over all orders, calculating total and then filtering.

Pretty neat!

Code for this blog can be found in an executable gist here.