schnittchen

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

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

fuelen

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

D4no0

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.

Where Next?

Popular in Discussions Top

jswny
I would like to better understand what the advantages/disadvantages of umbrella applications are compared to structuring your app as as s...
New
Jayshua
I recently came across the javascript library htmx. It reminded me a lot of liveview so I thought the community here might be interested....
New
blackode
Elixir Upgrading is so Simple in Ubuntu and It worked for me Ubuntu 16.04 git clone https://github.com/elixir-lang/elixir.git cd elixir...
New
WildYorkies
It seems that the more I read, the more I find Elixir users speaking about all the ways that Elixir is not good for x, y, and z use cases...
New
arcanemachine
https://nitter.net/josevalim/status/1744395345872683471 https://twitter.com/josevalim/status/1744395345872683471
New
IVR
Hi all, I’ve seen a number of related threads in the past, but I’d still be very curious to hear an up-to-date opinion on this topic. I...
New
eteeselink
Hi all, In the last days, two things happened: A blog post titled “They might never tell you it’s broken” made the rounds. It’s about ...
New
wmnnd
The Go vs Elixir thread got me thinking: Would it be too hard to implement a simple mechanism for creating Go-style static app binaries f...
New
opsb
We’re considering our architecture from a viewpoint of scaling our traffic heavily over the next 6 months. Our current deployment is runn...
New
AstonJ
Seen any cool LiveView demos, sample apps or examples? Please post them here! :003:
New

Other popular topics Top

TunkShif
This post is an instruction guide to help you setup your Neovim for Elixir development from scratch. It includes general information on h...
274 41539 114
New
alice
Hey, Just curious what are the main benefits of Elixir compared to Clojure? When is Elixir more useful than Clojure and vice versa? Th...
New
belgoros
I’m not a pro in using Regex and can’t figure out why the following behaviour happens, especially if we take into account the difference ...
New
grych
Hi folks, Few months ago I have announced the proof-of-concept of the library to manipulate the browsers DOM objects directly from Elixi...
639 52341 488
New
RisingFromAshes
I’ve read in another post that it may be possible with a router helper - but I couldn’t find an appropriate one, and tbh, I’m still just ...
New
Qqwy
Original source of discussion: This topic on the Pragmatic Programmers’ Functional Web Development with Elixir, OTP, and Phoenix forum. ...
New
joaquinalcerro
Hi there, I am working with Ecto-Postgresql and I need to call all of the records from a specific table but the table has 40,000 records...
New
Qqwy
Update: How to use the Blogs &amp; Podcasts section You can post links to your blog posts or podcasts either in one of the Official Blog...
3271 126479 1222
New
jononomo
For some reason my phoenix channels are working for me in my local dev environment, but as soon as I deploy via Docker, I get a 403 error...
New
lanycrost
Hi everyone! I need implement if…else if…else condition from my elixir code, and anymore of this control flow structures not work proper...
New

Latest on Elixir Forum

We're in Beta

About us Mission Statement