How to use postgres identity columns in Ecto?

I’m currently trying to use Postgres’s identity columns for auto-incrementing primary keys, instead of serial, but I’m unsure how to get it to work properly. First, I tried setting it in the config with

config :myapp, MyApp.Repo,
  migration_primary_key: [name: :id, type: :"bigint GENERATED ALWAYS AS IDENTITY"]

But that, unfortunately, causes any foreign keys to that resource to also be identity columns (here’s the SQL from a migration to create a table with a reference to another).

"post_id" bigint GENERATED ALWAYS AS IDENTITY NOT NULL CONSTRAINT "votes_post_id_fkey" REFERENCES "posts"("id")

I also tried setting the type manually in the migration with

create table(:posts, primary_key: false) do
      add :id, :"bigint GENERATED ALWAYS AS IDENTITY", primary_key: true, null: false
end

But that causes a postgrex error probably because of the extra stuff after the column type. I could manually do it with execute, but that’s a lot of work and not an ideal solution.

1 Like

have you tried using it as default value?

add :id, :bigint, primary_key: true, null: false, default: fragment("GENERATED ALWAYS AS IDENTITY")

i’ve never tested it like that, but i guess it should work.

Unfortunately, that causes a different error

(Postgrex.Error) ERROR 42601 (syntax_error) syntax error at or near "ALWAYS"

because it puts the text DEFAULT before the fragment and GENERATED ALWAYS AS IDENTITY is not a value, but an option for a column (see the where column_constraint is: section of https://www.postgresql.org/docs/10/sql-createtable.html).

probably the best way to handle that gonna be writing raw sql stuff and using execute/1 https://hexdocs.pm/ecto_sql/Ecto.Migration.html#execute/1