create rails migration for PostgreSQL GENERATED ALWAYS AS column

Question

How to create rails migration for auto calculated columns like below.

    a_column integer,
    b_column integer,
    total1 integer GENERATED ALWAYS AS (a_column+b_column) stored,
    total2 integer GENERATED ALWAYS AS (a_column-b_column) stored,
    total3 integer GENERATED ALWAYS AS (a_column/b_column) stored
);

I don't want to do using Models, because I want to use same table from another system having different technology.

mechnicov · Accepted Answer · 2021-09-01 15:08:46Z

2

You can use pure SQL in migration

def up
  execute <<~SQL
    CREATE TABLE table_name (
      a_column integer,
      b_column integer,
      total1 integer GENERATED ALWAYS AS (a_column+b_column) stored,
      total2 integer GENERATED ALWAYS AS (a_column-b_column) stored,
      total3 integer GENERATED ALWAYS AS (a_column/b_column) stored
    );
  SQL
end

def down
  drop_table :table_name
end

When using such migrations, in order for the database schema to be correct, you need to use SQL dump type (default type is Ruby). To setup this setting add line to your config/application.rb

config.active_record.schema_format = :sql

After that schema will be saved to db/structure.sql

Please read more

edited Sep 1, 2021 at 15:08

answered Sep 1, 2021 at 11:19

mechnicov

16.2k5 gold badges48 silver badges69 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

max Over a year ago

You should also change the schema dumps to SQL instead of schema.rb or the information will get lost or it might blow up. blog.appsignal.com/2020/01/15/…

mechnicov Over a year ago

Thanks @max. I added information to the answer

Collectives™ on Stack Overflow

create rails migration for PostgreSQL GENERATED ALWAYS AS column

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related