I have a psql db with several tables - I'm trying to hoist a child table's column into its parent table. In the process, I ran into some trouble trying to update an index on the child table. Not my exact code, but a close illustration (thanks in advance for forgiving any sqlalchemy misunderstandings):
class Parent(db.Model):
__tablename__ = "parent_table"
id = Column(
UUID, primary_key=True, default=uuid.uuid4, **kwargs
)
# other table that is not inherited here
foo_id = Column(UUID, nullable=False)
foo = relationship(
Foo,
foreign_keys=foo_id,
primaryjoin=(Foo.id == foo_id),
backref=backref(
"items", cascade="all, delete-orphan", passive_deletes=True
),
)
# the hoisted values:
moved_at = Column(DateTime, default=sql.null())
is_moved = column_property(moved_at != sql.null())
class Child(Parent):
__tablename__ = "child_table"
id = Column(
ForeignKey(Parent.id, ondelete="CASCADE"), primary_key=True
)
name = Column(Text, nullable=False)
location = Column(
Text, nullable=False, server_default=""
)
# removed `moved_at` and `is_moved`
__mapper_args__ = {
"polymorphic_identity": "..."
}
__table_args__ = (
Index(
"ux_child_id_name",
id,
name,
unique=True,
postgresql_where=(~Parent.moved_at.isnot(None)),
),
)
I removed moved_at
and is_moved
from Child
and put it in Parent
. The index's condition prior to hoisting was postgresql_where=(~moved_at.isnot(None)),
.
I then run alembic migrations updating the db structure, and execute my unit tests (using pytest), but I get the following:
sqlalchemy.exc.ProgrammingError: (psycopg2.errors.UndefinedColumn) column "moved_at" does not exist
I can see that the attempt produces the following, so I know I must be doing the inheritance or something else wrong, since it doesn't seem to access the parent table at all:
[SQL: CREATE UNIQUE INDEX ux_child_id_name ON
child_table (id, name) WHERE moved_at IS NOT NULL]
There may be some other thing that I'm neglecting to include that may be a clue, but I don't know. Anything immediately wrong here?
I considered moving the index up to the Parent table, but I need the name
column from the Child table present in the index. I tried bringing the Index out of __table_args__
so it appears like this outside of the Parent model altogether:
Index(
"ux_child_id_name",
Child.id,
Child.name,
unique=True,
postgresql_where=(~Parent.moved_at.isnot(None)),
),
... but I still get the same error. I couldn't find anything so far in the docs.