Ecto migrations: How to create covering index using INCLUDE in PostgresQL?

From PostgresQL documentation:
https://www.postgresql.org/docs/current/indexes-index-only-scans.html

I should be able to do:
CREATE INDEX tab_x_y ON tab(x) INCLUDE (y);

So I tried doing:
create(index(:tab, [:x], include: [:y]))

But it seems to ignore the include part.

Is this supported in Ecto, or am I doing something wrong?

Here is doc for index/3 https://hexdocs.pm/ecto_sql/Ecto.Migration.html#index/3
There doesn’t seem to be an option like include.

I think you can do it by executing raw sql with execute/1 or execute/2
https://hexdocs.pm/ecto_sql/Ecto.Migration.html#execute/2

2 Likes

There is one, now :slightly_smiling_face:

  • :include - specify fields for a covering index. This is not supported by all databases. For more information on PostgreSQL support, please read the official docs.