High Performance PostgreSQL for Rails: Storing Transformations in Generated Columns (page 85)

Pragmatic Bookshelf PragProg Customers
,
1

@andatki

The correct migration code for ruby/migration_snippet_virtual_column.rb is

class AlterTempCustomers < ActiveRecord::Migration[7.1]
  def change
    add_column 'temp.customers',
               :email_downcased, :string,
               as: 'LOWER(email)',
               stored: true
  end
end

This is because the table has already been created in the previous section. And, the book (page 85) says "Create a column for the temp schema customers table you’ve just made that adds a second column called email_downcased." The migration should be to add a column not to re-create an already existing table.

Besides, running the code from book (p.85) as-is returns error

PG::DuplicateTable: ERROR:  relation "customers" already exists
1 Like
2

Thank you for reporting this @santusgee. That’s indeed a mistake and was likely due to writing examples out of order. We’ll get a fix incorporated for the next ebook release and I’ll leave this open until that’s ready to go, and can follow up.

3

Hi @sanctusgee. I’m submitting an update to the book text based on your post here. Hopefully in lands in a new version soon.

I changed the migration code snippet you mentioned away from create_table and to add_column as follows:

add_column 'temp.customers',
  :email_downcased,
  :string,
  as: 'LOWER(email)',
  stored: true

As you said, the temp.customers (temp schema, customers table) table should exist already. I re-made it in my local testing environment quick like this:

create table if not exists temp.customers (id integer, email text);

With that snippet, I generated a dummy “Foo” Rails migration.

I pasted the code above into the change method and ran it:

-- add_column("temp.customers", :email_downcased, :string, {:as=>"LOWER(email)", :stored=>true})
   (3.1ms)  ALTER TABLE "temp"."customers" ADD "email_downcased" character varying GENERATED ALWAYS AS (LOWER(email)) STORED /*application='Rideshare'*/

Now when I \d temp.customers, I see the generated, stored virtual column, with the LOWER() function for email.

Look good?

Thanks again!