High Performance PostgreSQL for Rails

Storing Transformations in Generated Columns

PostgreSQL 12 added a feature called generated columns and it’s supported by Active Record as “virtual” columns.^[97] A virtual column is a second column that you create in your schema, but then PostgreSQL handles keeping it updated automatically on your behalf. It does that by transforming a value based on an expression that you’ve provided.

Let’s see how it works.

Create a column for the temp schema customers table you’ve just made that adds a second column called email_downcased.

The email_downcased gets the result of calling the SQL LOWER() function on the email column value. Whenever the email column is modified, email_downcased is updated automatically. This is a “stored” generated column. Here’s ...