How to handle Postgres generated columns in Ecto?

Chat & Discussions Discussions
,
3

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