PostgreSQL sequence nextval

If you need an escape hatch, Ecto provides fragments (see Ecto.Query.API.fragment/1 ) to inject SQL (and non-SQL) fragments into queries.

Hello @gcauchon,

Thanks for your response.

Regards,

Jerry

In case someone came across this question, a concrete solution is using fragment but it should be in from

import Ecto.Query
MyApp.Repo.one(
    from seq in fragment("SELECT nextval('slugs_id_seq') as id"), select: seq.id
)

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.

Interesting approach @arcanemachine !

A word of caution about retrieving the last value - there’s an important concurrency consideration to keep in mind. The get_next_available_id approach can lead to multiple processes request an ID simultaneously and get the same ID.