Ash Elixir•3y ago

Support for Postgres generated columns

Postgres supports creating columns that are generated when the row is inserted, updated. In my case, I have a property that has its address separated into multiple columns and I want to create a special field that will have the concatenation of all these fields. I was wondering if AshPostgres has support for that feature. What I tried so far was creating a specific statement to create the field in the migration:

      statement :create_normalized_full_address_field do
        up "alter table properties add column normalized_full_address generated always as (street_name || ' ' || city) stored;"
        down "alter table properties drop column normalized_full_address;"
      end

      statement :create_normalized_full_address_field do
        up "alter table properties add column normalized_full_address generated always as (street_name || ' ' || city) stored;"
        down "alter table properties drop column normalized_full_address;"
      end

But I don't know how to add that attribute into the resource in a way that will not create it already when generating the migration file. Also, it would be great to have a way to do it without using a custom statement, but I'm fine with that.

10 Replies

TechnoMage•3y ago

If you create a computation with the expr macro you should be able to push that to the database or use it in Elixir. This does not use Postgres cmoputed column, but pushes it in the select statement.

BlibsOP•3y ago

Yeah, but that would be calculated at runtime, meaning that it would be very inefficient to use it to filter data

ZachDaniel•3y ago

Expression calculations are done in the query Oh I see what you mean It’s just as efficient as sql, but a generated column would be more efficient than that yes You can add the column to your resource and then ignore that column in the migration generator

ZachDaniel•3y ago

https://ash-hq.org/docs/dsl/ash-resource#postgres-migration_ignore_attributes

Ash HQ

Ash.Resource

View the documentation for Ash.Resource on Ash HQ.

BlibsOP•3y ago

Thanks, that's exactly what i needed 😄

kernel•3y ago

problem with generated columns is AFAIK you can't change the definition after the fact I personally add an attribute with generated?: true and define the expression in a migration

ZachDaniel•3y ago

WDYM you can’t change the definition after the fact? Wouldn’t you just hand write another migration?

kernel•3y ago

https://dba.stackexchange.com/questions/250868/how-to-change-definition-of-a-postgres-generated-stored-column not possible unless you drop the column and recreate it

BlibsOP•3y ago

Isn't that fine? I mean, you will drop it and recreate and postgres will regenerate all the rows with the new definition

kernel•3y ago

sometimes, I would personally be annoyed in having to drop columns, but if the tool does it automatically then maybe 🤷🏿‍♂️

Gaming

Programming

Support for Postgres generated columns

Did you find this page helpful?