nicnilov

Difficulty employing Duration to PostgreSQL :interval with solution

This is not so much a question but rather an answer to an issue which took me some time to figure out, and maybe a suggestion to improve the docs. Please see the end of the message for the solution.

Using Elixir 1.17.3, Ecto 3.12.4, Postgrex 0.19.3:

I’m working on some calendar-related functionality and needed to represent a duration. I had this in my migration:

create table(...) do
  ...
  add :default_duration, :interval
  ...

However, it was not clear to me how should I define my schema. For starters I was looking to make the field load as just a Postgrex.Interval, which seems to be the default and should not require any extra configuration.

I could not just do this:

schema "..." do
  ...  
  field :default_duration, :interval
  ...

as there is no :interval Ecto type. When I did this:

  field :default_duration, :duration

it compiled, but an attempt to load a record resulted in an error:

(ArgumentError) cannot load `%Postgrex.Interval{months: 0, days: 0, secs: 1800, microsecs: 0}` as type :duration for field :default_duration...

I tried field :default_duration, Postgrex.Interval, field :default_duration, Postgrex.Duration, even field :default_duration, Postgrex.Extensions.Interval with the same result.

Following the Ecto.Schema docs, Postgrex docs, Postgrex.Types.html#define/3 docs I tried to configure Postgrex to use the interval_decode_type: Duration, however the docs seemed rather sparse and it was not clear how exactly it should be done. I tried this in my lib/my_app/postgrex_types.ex:

Postgrex.Types.define(MyApp.PostgrexTypes, [], interval_decode_type: Duration)

However it is also additionally required passing types: MyApp.PostgrexTypes to Postgrex.start_link/1, and since I don’t start Postgrex manually, it was not clear to me how this bit could be passed to it in addition to all the existing Repo config.

It was not until I inadvertently looked into Ecto.Adapters.Postgres and saw the Extensions clause when it finally became clear. So, in order to employ the Duration Elixir type on a Postgres :interval field without registering any new extensions, the following worked:

In lib/my_app/postgres_types.ex:

Postgrex.Types.define(MyApp.PostgresTypes, Ecto.Adapters.Postgres.extensions(), interval_decode_type: Duration)

In config/config.exs:

config :my_app, MyApp.Repo,
  ...
  types: MyApp.PostgresTypes

At this point a schema field defined as

  field :default_duration, :duration

started returning the desired Duration struct:

  ...
  default_duration: %Duration{minute: 30, microsecond: {0, 6}},
  ...

Maybe the solution is so obvious no one really had an issue before - I didn’t find any complaints online. But maybe the Ecto.Schema | Primitive types should be updated to include a more explicit example.

6 comments

#postgrex

14 248 6

2025-02-20 20:45:21 UTC

Most Liked

christhekeele

I disagree, per your own point that

I’d say this extends to documentation on postgre(s|x) exclusive features: that information should live in adapter-specific docs, which could be improved for this specific stlib Duration feature of Postgrex the author (and later, I) was struggling with.

Or are you simply referring to Ecto documenting somewhere the information that schema and migration DSLs are not guaranteed to align? I agree that might be helpful to newcomers to Ecto! Just pointing out that that concept is unrelated to this post.

I think you might be confused about what exactly the author was struggling with: the correct incantation of Postgrex.Types.define/3 to get schemas to hydrate :duration fields into stdlib Duration structs instead of Postgrex.Duration ones. It’s a feature that Postgrex documents exists, but does not provide enough information to leverage. It’s unrelated to schema/migration DSL differences or any confusion around that.

Post #5

D4no0

OK, this makes more sense now. I always thought that postgrex was strictly doing the plumbing between ecto ↔ postgres, this is the first time I hear about it offering extensions for postgres specific types that can be used in ecto, even though logically that makes perfect sense.

Post #6

nicnilov

I apologize for not making my original post clear enough to highlight the main point, and thank @christhekeele for explaining it with such precision. While I agree the Postgrex documentation should be improved, I don’t expect to do it myself, so the task is up for grabs for anyone who wants it.

Post #7

D4no0

First of all I am happy that you found the solution, but this is not an actual issue of Ecto, but a fact of life that you have used postgres specific functionality. A PR to improve awareness of this to newcomers would be more welcome!

nicnilov:

I’m working on some calendar-related functionality and needed to represent a duration. I had this in my migration:
create table(...) do
  ...
  add :default_duration, :interval
  ...

Per-documentation this is supported out of the box, more info you can find in add/3.

That’s absolutely not true, ecto is a generic library that supports many database engines, there is no reason ecto would support postgres features exclusively.

Ecto schemas and migrations are not linked in any way, please read the disclaimer in the link above. There are reasons why it was implemented like this and one of those is flexibility (note in documentation how you can define random custom database types in migrations).

Post #2

D4no0

Good points!

I still think ecto should be responsible for covering this kind of information, otherwise it’s really hard to find the documentation, as migrations even though are not linked in any way to schemas, they are still part of ecto functionality.

Post #4