PostgreSQL sequence nextval

Questions / Help
1

Hello,

Is there a way I can access PostgreSQL sequence nextval from Elixir Ecto query?

I would be very grateful for your response.

Thanks.

Jerry

2

https://hexdocs.pm/ecto/Ecto.Query.html#module-fragments

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

1 Like
3

Hello @gcauchon,

Thanks for your response.

Regards,

Jerry

4

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

6

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.

2 Likes