Home Ask Login Register

Developers Planet

Your answer is one click away!

Jean-Pierre Bécotte February 2016

How to use Postgres' enumerated type with Ecto

With PostgreSQL, we can do something like this:

CREATE TYPE order_status AS ENUM ('placed','shipping','delivered')

From Ecto's official doc, there is no native type to map the Postgres' enumerated type. This module provides a custom type for enumerated structures, but it maps to an integer in the database. I could easily use that library, but I would prefer using the native enumerated type that ships with the database.

Ecto provides also a way to create custom types, but as far as I can see, the custom type must map to a native Ecto type...

Anyone knows if this can be done in a schema with Ecto? If yes, how would the migration work?


asonge February 2016

You need to create an Ecto type for each postgresql enum. In the schema definition, you simply have the type be :string. In migrations, you set the type to be the module name. This can get really tedious, though, so I have the following macro in my project that uses Postgresql enums:

defmodule MyDB.Enum do

  alias Postgrex.TypeInfo

  defmacro defenum(module, name, values, opts \\ []) do
    quote location: :keep do
      defmodule unquote(module) do

        @behaviour Postgrex.Extension

        @typename unquote(name)
        @values unquote(values)

        def type, do: :string

        def init(_params, opts), do: opts

        def matching(_), do: [type: @typename]

        def format(_), do: :text

        def encode(%TypeInfo{type: @typename}=typeinfo, str, args, opts) when is_atom(str), do: encode(typeinfo, to_string(str), args, opts)
        def encode(%TypeInfo{type: @typename}, str, _, _) when str in @values, do: to_string(str)
        def decode(%TypeInfo{type: @typename}, str, _, _), do: str

        def __values__(), do: @values

        defoverridable init: 2, matching: 1, format: 1, encode: 4, decode: 4

        unquote(Keyword.get(opts, :do, []))


Possible usage:

import MyDB.Enum
defenum ColorsEnum, "colors_enum", ~w"blue red yellow"

ColorsEnum will be the module name, "colors_enum" will be the enum name internal to Postgresql: you will need to add a statement to create the enum type in your database migrations. The final argument is a list of enum values. I used a ~w sigil that will split the string by whitespace to show how concise this can be. I also added a clause that converts atom values to string values when they pass through an Ecto schema.

Post Status

Asked in February 2016
Viewed 3,784 times
Voted 12
Answered 1 times


Leave an answer

Quote of the day: live life