How to use postgresql table inheritance with ecto

Questions & Help Questions
1

Postgresql supports table inheritance which allows to create tables like this:

CREATE TABLE cities (
  name       text,
  population real,
  altitude   int    

CREATE TABLE capitals (
  state      char(2)
) INHERITS (cities);

and to query these tables like this:

SELECT name, altitude
  FROM cities
  WHERE altitude > 500;

and like this:

SELECT name, altitude
    FROM ONLY cities
    WHERE altitude > 500;

How do we write the same using Ecto?

2

In migration:

create table(:cities) do
  add :name, :text,
  add :population, :integer,
  add :altitutde, :integer
end

create table(:capitals, options: "INHERITS (cities)") do
  add :state, :text
end

And then you can query it like:

from city in "cities",
  where: city.altitude > 500,
  select: %{
    name: city.name,
    altitude: city.altitude
  }

Second query thought is not possible with Ecto.Query right now (AFAIK) and you need to use raw SQL, ex.:

{:ok, result} =
  Repo.query("""
  SELECT name, altitude
      FROM ONLY cities
      WHERE altitude > ?
  """, [500])

Enum.map(result.rows, fn [name, altitude] -> %{name: name, altitude: altitude} end)
3

Thanks!

4

Yeah as it stands Ecto is not capable of handling PostgreSQL inheritance tables, you have to fall back to raw SQL, thus losing the typing and testing abilities of Ecto. A PR might be welcome though? :slight_smile:

5

I think its still not supported in ecto. I would love to submit a PR for it. if you have specific instructions or steps in mind, please do share. Thanks

6

It would be better to see if there is a way to make an addon library for it instead, Ecto is mostly in ‘keep-it-updated’ mode rather than accepting specific DB server features.

7

But I am not sure, what should be the best way to create some add on. eg I dont know if ecto provides some hooks to change query just before sending to db(I am not sure, if this is even recommened). some steps/guidelines(maybe someone from core team eg @josevalim @ericmj @michalmuskala ) will be really helpful as I dont find any clue yet(prepare_query or other callbacks dont seem to be the correct ones). Thanks

8

I think there’d be some way to do it, but if there isn’t then making the appropriate hooks with their inputs on the design would be very likely to be accepted I would bet. :slight_smile: