PostgreSQL sequence nextval

Questions & Help Questions
5

I was messing around with this recently, and I was having difficulty getting the next available sequence ID without incrementing the sequence in situations where there may or not be any rows in the table (e.g. during testing).

I came up with this, so I’m adding it to the pile in case it may be useful as well.

defmodule YourProject.Repo.YourSchemaIdSeqs.YourSchemaIdSeq do
  @moduledoc "The Postgres sequence used to generate YourSchema IDs."

  use Ecto.Schema
  alias YourProject.Repo
  import Ecto.Query

  @primary_key false
  schema "your_schema_id_seq" do
    field :last_value, :integer
  end

  @doc "Return the next value in the sequence."
  def get_next_available_id do
    if Repo.exists?(SomeSchema),
      do: Repo.one!(from ysis in __MODULE__, select: ysis.last_value) + 1,
      else: Repo.one!(from ysis in __MODULE__, select: ysis.last_value)
  end
end

This was the product of me messing around, and there may be a simpler way, but it worked for me at the time.