schnittchen
How to handle Postgres generated columns in Ecto?
I have a table with a generated column (PostgreSQL: Documentation: 18: 5.4. 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?
Most Liked
fuelen
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
}
fuelen
Try to add read_after_writes: true option to the field. Maybe I test it incorrectly, but it works fine for me ![]()
D4no0
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.
Popular in Discussions
Other popular topics
Categories:
Sub Categories:
Forums
Popular Tags
- #ecto
- #liveview
- #troubleshooting
- #learning-elixir
- #deployment
- #library
- #erlang
- #testing
- #genserver
- #mix
- #absinthe
- #remote-other
- #otp
- #plug
- #how-to-question
- #macros
- #postgres
- #channels
- #elixirconf
- #exunit
- #discussion
- #code-sync
- #javascript
- #podcasts
- #onsite
- #dialyzer
- #docker
- #authentication
- #umbrella
- #full-time-contract
- #podcasts-by-brainlid
- #ecto-query
- #elixir-ls
- #phoenix_html
- #iex
- #blog-post
- #graphql
- #genstage
- #ai
- #websockets
- #supervisor
- #advent-of-code
- #elixirconf-us
- #distillery
- #processes
- #forms
- #api
- #metaprogramming
- #security
- #performance









