Reference data table in PostgreSQL schema

Questions / Help
1

I have a collection of (basically static) reference data for my app, which is loaded separately into a Postgres schema reference_data. I want to query data from tables in that schema in my app’s Ecto queries. My first attempt was:

from parcel in "reference_data.living_england_habitat_map_phase_4",
    select: [parcel.ogc_fid],
    where: ...

When I run this query, I get an error:

[error] GenServer #PID<0.872.0> terminating
** (Postgrex.Error) ERROR 42P01 (undefined_table) relation "reference_data.living_england_habitat_map_phase_4" does not exist

    query: SELECT r0."ogc_fid", r0."id", r0."a_pred", r0."b_pred", r0."geom" FROM "reference_data.living_england_habitat_map_phase_4" AS r0 WHERE ...  LIMIT 10

The table does exist in that database, and I can query it from psql. If I swap the table reference to be a table in the public schema, I don’t get this error. So, if I’m understanding this correctly, I can’t access my reference_data schema table using:

from: parcel in "reference_data.living_england_habitat_map_phase_4",

because that generates as SQL

SELECT r0."ogc_fid" FROM "reference_data.living_england_habitat_map_phase_4"

instead of FROM reference_data.living_england_habitat_map_phase_4 without double-quotes.

What should I be doing instead?

2

The @schema_prefix option sounds like what you’re looking for.

3 Likes
3

Thanks @al2o3cr. I hadn’t actually declared a schema for that table, but having done so and added the @schema_prefix, my query is working.

According to some StackOverflow posts that I read, it is OK to query a table directly without creating an Ecto schema for it first, so I still don’t know if it’s possible to directly reference (without an Ecto schema) a table in a (Postgres) schema. But maybe the answer is “well, don’t do that” :slight_smile:

4

It is possible:

Repo.all(query, prefix: "reference_data")
2 Likes
5

Ah, thanks @hauleth!