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.