Querying "information_schema" Postgres schema with Ecto queries?

Questions / Help
, ,
1

I am trying to list all tables and columns of a Postgres database for some analysis.

I have written the following code:

import Ecto.Query

from(f in "information_schema.columns", select: [:table_name, :column_name, :data_type])
|> DB.Repo.all()
|> IO.inspect()

Which raises an error:

** (Postgrex.Error) ERROR 42P01 (undefined_table) relation "information_schema.columns" does not exist
    query: SELECT i0."table_name", i0."column_name", i0."data_type" FROM "information_schema.columns" AS i0

Executing the same query as a raw SQL query works:

Ecto.Adapters.SQL.query!(
  DB.Repo,
  "SELECT table_name, column_name, data_type from information_schema.columns"
)
|> IO.inspect()

I presume there is some complexity related to the use of a Postgres schema (information_schema) which is different from public.

Is there a way to make it work? Thanks!

– Thibaut

2

Hey @thbar this is what the prefix option is for:

from(f in "columns", select: [:table_name, :column_name, :data_type], prefix: "information_schema")

For more information see: Ecto.Query — Ecto v3.8.4

3 Likes
3

Hey @benwilson512, thanks for the fast response! It works, many thanks!

This makes me wonder if the error message could be improved to hint about that…