Using PostgreSQL Identity Columns in Ecto SQL
Using Postgres Identity Columns in Ecto SQL
When working with PostgreSQL and Elixir’s Ecto ORM, you are likely using SERIAL
columns for auto-incrementing primary keys. However, since PostgreSQL 10 (released in October 2017), there’s a better way: IDENTITY
columns.
Why Identity Columns?
Identity columns (GENERATED AS IDENTITY
) are part of the SQL Standard and are the recommended approach for auto-incrementing keys in modern Postgres. While I won’t dive into why SERIAL
columns should be avoided (check out Stop using SERIAL in Postgres for details), let’s explore how to use IDENTITY
columns with Ecto.
Repository-Wide Configuration
The simplest way to use identity columns is to configure them for your entire Ecto repository. Add this to your config:
config :demo, Demo.Repo,
migration_primary_key: [type: :identity]
Now, when you create a table:
defmodule Demo.Repo.Migrations.CreateUsers do
use Ecto.Migration
def change do
create table(:users) do
add :email, :string
add :name, :string
timestamps(type: :utc_datetime)
end
create unique_index(:users, [:email])
end
end
Ecto will generate SQL using an identity column:
CREATE TABLE "users" (
"id" bigint GENERATED BY DEFAULT AS IDENTITY,
"email" varchar(255),
"name" varchar(255),
"inserted_at" timestamp(0) NOT NULL,
"updated_at" timestamp(0) NOT NULL,
PRIMARY KEY ("id"))
Per-Table Configuration
Need identity columns for specific tables only? You can configure them individually in your migrations:
defmodule Demo.Repo.Migrations.CreateUsers do
use Ecto.Migration
def change do
create table(:users, primary_key: false) do
add :id, :identity, primary_key: true
add :email, :string
add :name, :string
timestamps(type: :utc_datetime)
end
create unique_index(:users, [:email])
end
end
This gives you the flexibility to use identity columns exactly where you need them.
That’s all there is to it!
Using UUIDs Instead
If you prefer using UUIDs for your primary keys, you can easily adapt the above patterns by changing :identity
to :binary_id
. For repository-wide configuration:
config :demo, Demo.Repo,
migration_primary_key: [type: :binary_id]
Or for individual tables:
defmodule Demo.Repo.Migrations.CreateUsers do
use Ecto.Migration
def change do
create table(:users, primary_key: false) do
add :id, :binary_id, primary_key: true
add :email, :string
add :name, :string
timestamps(type: :utc_datetime)
end
create unique_index(:users, [:email])
end
end