I looked at the recent changes of Rails and Activerecord and spotted an interesting entry:
Support for generated columns in PostgreSQL.
In this post I’ll show an example how to use generated columns with Rails 7 and PostgreSQL.
Let’s create a new table
Humans with the stored generated column
class CreateHumans < ActiveRecord::Migration[7.0] def change create_table :humans do |t| t.integer :weight_in_kilos t.integer :height_in_cm # Generated columns bmi_sql_function = 'weight_in_kilos / (height_in_cm::float / 100) ^ 2' t.virtual :bmi_stored, type: :integer, as: bmi_sql_function, stored: true end end end
Let’s create a fairly realistic row in the Rails console:
rails console human = Human.create(weight_in_kilos: 83, height_in_cm: 174) human.reload puts human.bmi_stored => 27
So instead of calculating the bmi on the human model in a method we moved this logic to the database layer.
Whenever we update the row, the bmi gets recalculated and saved in the
There is one downside to using generated columns right now: You need to reload your model instance to get access to the updated value.
The following example demonstrates this:
human = Human.first # loose some weight human.decrement!(:weight_in_kilos, 15) puts human.bmi_stored => 27 pust human.reload.bmi_stored => 22
It would be theoretically possible to avoid the
reload and there is already an open pull request tackling this problem: Implement returning for postgresql
stored: true vs.
There’s also the non-stored variant of this type of column, which would be used if we dropped the
stored: true argument or set it to
This variant would instead of saving the calculated bmi calculate it on the fly whenever we want to read it.
To understand the difference, I implemented the concepts of both variants in pure Rails:
While the non stored column would act more like a getter that calculates the value on the fly,
class Human def bmi (weight_in_kilos / ( height_in_cm.to_f / 100) ** 2).to_i end end
the stored variant saves the calculated value whenever we update the row:
class Human before_save :calculate_bmi private def calculate_bmi self.bmi_stored = (weight_in_kilos / (height_in_cm.to_f / 100) ** 2).to_i end end
Not every database does support both variants yet and there’s also not every type supported in ActiveRecord.
Current support in Rails
The following table represents the current support of the stored and non-stored column variants in Rails:
|Database||Virtual column support||Stored column support|
While newer versions of SQLite support both column variants, the ActiveRecord sqlite adapter does not support them yet.
Reach out to me if you have questions or comments