How to use postgres identity columns in Ecto?

Questions / Help
,
1

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
2

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.

3

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 PostgreSQL: Documentation: 10: CREATE TABLE).

4

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

5

@Un3qual, is it resolved? I am trying to run the same but via execute.
I am always getting postgres error, i understand its not a ecto issue, but how to add generated columns? No examples on this at ecto docs.

I am trying to run this:

ALTER TABLE USERS  ALTER COLUMN AGE ADD GENERATED ALWAYS AS (extract year from age(now(), dob)) STORED; => error

ALTER TABLE USERS DROP COLUMN AGE;
ALTER TABLE USERS ADD COLUMN AGE TYPE INT GENERATED ALWAYS AS (extract year from age(now(), dob)) STORED; => error

Error: 

* (Postgrex.Error) ERROR 42601 (syntax_error) syntax error at (2 errors)
1. or near "INT" 
2. or  near "GENERATED"
7

For anyone else who comes this way, :identity has been accepted as a column type in ecto since 2020: Support for identity key types in Postgres 10 or later by jamesvl · Pull Request #255 · elixir-ecto/ecto_sql · GitHub

1 Like