Difficulty employing Duration to PostgreSQL :interval with solution

Questions / Help
, ,
1

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.

1 Like
2

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!

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).

1 Like