How to handle Postgres generated columns in Ecto?

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

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
}

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.

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.