Cannot select field from inherited table

[jaulz]

Environment

• PostgreSQL version: PostgreSQL 15.1 (Ubuntu 15.1-1.pgdg20.04+1) on aarch64-unknown-linux-gnu, compiled by gcc (Ubuntu 9.4.0-1ubuntu1~20.04.1) 9.4.0, 64-bit
• PostgREST version: v12.0.1
• Operating system: Ubuntu

Description of issue

I have these tables (note that two of them are inherited):

CREATE TABLE "public"."countries" (
    "code" text,
    "name" text,
    PRIMARY KEY ("code")
);

CREATE TABLE "public"."legal_persons" (
    "id" uuid NOT NULL DEFAULT gen_random_uuid_v7(),
    "name" text,
    "country_code" text NOT NULL REFERENCES "public"."countries"(code),
    PRIMARY KEY ("id")
);

CREATE TABLE "public"."entities" (
    "entity_type_id" uuid
) INHERITS ("public"."legal_persons");

CREATE TABLE "public"."natural_persons" (
    "born_at" date
) INHERITS ("public"."legal_persons");

I expected I could query entities and get its country as well:

GET /rest/v1/entities

select: id,name,countries(name)
offset: 0
limit: 10

However, I see this error:

{
    "code": "PGRST200",
    "details": "Searched for a foreign key relationship between 'entities' and 'countries' in the schema 'public', but no matches were found.",
    "hint": "Perhaps you meant 'accounts' instead of 'countries'.",
    "message": "Could not find a relationship between 'entities' and 'countries' in the schema cache"
}

Is this simply not possible? Thanks!

[wolfgangwalther]

This is a PostgreSQL thing. When you INHERIT a table, the FKs are not inherited at the same time - thus we can't detect any relationships.

You'll need to add the same FK in the entities and natural_persons tables as well - not only for PostgREST, but also for your data validation.

[jaulz]

Thanks a lot for the hint! ☺️