How to handle Postgres generated columns in Ecto?

I have a table with a generated column (PostgreSQL: Documentation: 16: 5.3. Generated Columns).

So far I only used it inside queries using a fragment, which was sufficient since I never needed to read the value from a loaded schema, so I simply ommitted the field declaration for it.

Now that I want to load the value, I run into the problem that even when I only change a different field on an existing record, Ecto seems to generate an update for the value in the column, which Postgres correcly rejects with an error. What I would need is kind of the opposite of the load_in_query flag, to prevent that and treat the column as immutable (which would introduce the problem that I’d get back a stale value after an update, but that’s not my concern right now).

I searched but couldn’t find anything related, and it looks like I need to work around the problem for the time being.

Does anyone else have experience with exposing a generated column on an Ecto Schema?

Try to add read_after_writes: true option to the field. Maybe I test it incorrectly, but it works fine for me :slight_smile:

1 Like

Table

CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    full_name VARCHAR(101) GENERATED ALWAYS AS (first_name || ' ' || last_name) STORED,
    birth_date DATE,
    hire_date DATE,
    age_at_hire INT GENERATED ALWAYS AS (date_part('year', hire_date) - date_part('year', birth_date)) STORED
);

Insert sample data:

INSERT INTO employees (first_name, last_name, birth_date, hire_date)
VALUES 
('John', 'Doe', '1985-06-15', '2020-01-10')

Schema:

defmodule MyApp.Employee do
  use Ecto.Schema

  schema "employees" do
    field :first_name, :string
    field :last_name, :string
    field :full_name, :string, read_after_writes: true
    field :birth_date, :date
    field :hire_date, :date
    field :age_at_hire, :integer, read_after_writes: true
  end
end

Test:

> MyApp.Employee |> MyApp.Repo.one()
# [debug] QUERY OK source="employees" db=1.6ms
# SELECT e0."id", e0."first_name", e0."last_name", e0."full_name", e0."birth_date", e0."hire_date", e0."age_at_hire" FROM "employees" AS e0
%MyApp.Employee{
  __meta__: #Ecto.Schema.Metadata<:loaded, "employees">,
  id: 1,
  first_name: "John",
  last_name: "Doe",
  full_name: "John Doe",
  birth_date: ~D[1985-06-15],
  hire_date: ~D[2020-01-10],
  age_at_hire: 35
}

> MyApp.Employee |> MyApp.Repo.one() |> Ecto.Changeset.change(first_name: "JOHN") |> MyApp.Repo.update!()
# [debug] QUERY OK source="employees" db=2.1ms
# SELECT e0."id", e0."first_name", e0."last_name", e0."full_name", e0."birth_date", e0."hire_date", e0."age_at_hire" FROM "employees" AS e0
# [debug] QUERY OK source="employees" db=0.9ms
# UPDATE "employees" SET "first_name" = 'JOHN' WHERE "id" = 1 RETURNING "age_at_hire","full_name"
%MyApp.Employee{
  __meta__: #Ecto.Schema.Metadata<:loaded, "employees">,
  id: 1,
  first_name: "JOHN",
  last_name: "Doe",
  full_name: "JOHN Doe",
  birth_date: ~D[1985-06-15],
  hire_date: ~D[2020-01-10],
  age_at_hire: 35
}
2 Likes

You can simply use the option virtual: true, but beware that no actual checks will be done for that field, meaning that declaring a field is just to make sure that you will not get complains from ecto when selecting into a schema, for example:

field :test, :string, virtual: true

will work, however you can place any value in that field and ecto will never complain, that includes custom types, they will never even try to validate or transform the received result.

1 Like

Thanks, and sorry for the late reply. This is what it looks like for me, in the schema:

    # This field is GENERATED STORED, so it is read-only
    field :date_range, PgRanges.DateRange, read_after_writes: true

It’s been like this for a few weeks now and it works quite well.

I also wrote contains_day? and overlaps_date_range? macros to make using the date_range easier when querying. Those are far from perfect though.