Incorrect PSQL Quotes for postgresql_inherits (document quoted_name for this, and also clean up old things in quoted_name docs)

[marcuswishart]

Describe the bug
When inheriting off a postgresql table like this:

CREATE TABLE my_schema.cities (
  id int,
  name       text,
  population real,
  elevation  int     -- (in m),
  PRIMARY KEY (id),
);

CREATE TABLE my_schema.capitals (
  state      text UNIQUE NOT NULL
) INHERITS (my_schema.cities);

code from https://www.postgresql.org/docs/current/tutorial-inheritance.html.

Alembic generates this upgrade command,

op.create_table(
"capitals"
sa.Column("id", sa.Integer(), nullable=False),
sa.Column("state", sa.Text(), nullable=False),
sa.ForeignKeyConstraint(
["id"],
["my_schema.cities"]
),
sa.UniqueConstraint("state")

Emitting this PSQL code,

CREATE TABLE my_schema.capitals (
        id INTEGER NOT NULL,
        state      text UNIQUE NOT NULL,
        PRIMARY KEY (id),
        FOREIGN KEY(id) REFERENCES my_schema.cities (id),
)
 INHERITS ( "my_schema.cities" )

Which leads to two errors when it is then pushed to PSQL,

1. The table contains a column that already exists in the existing (cities) schema, however to make SQLAlchemy happy you need to have a PK in the table otherwise it throws an error.
2. The generated inheritance method should not double quotes, or if it does it should be in this format, i.e., "my_schema"."cities"

Expected behaviour

Alembic should recognise that a column is about to be double defined and ignore it from its schema, and also correctly handle double quotes.

To Reproduce
Please try to provide a Minimal, Complete, and Verifiable example, with the migration script and/or the SQLAlchemy tables or models involved.
See also Reporting Bugs on the website.

# Insert code here
Base = declarative_base(metadata=meta)

class Cities(Base):
   __tablename__ = "cities"

    id: Mapped[int] = mapped_column(
        Integer, primary_key=True, autoincrement=True
    )
    name: Mapped[str] = mapped_column(Text)
    population: Mapped[int] = mapped_column(Real)
    elevation: Mapped[int] = mapped_column(Integer)

class Capitals(Cities):
  __tablename__ = "cities"

    id: Mapped[int] = mapped_column(
        Integer, primary_key=True, foreign_key="cities.id"
    )
    state: Mapped[str] = mapped_column(Text, unique=True, nullable=False)

    __table_args__ = {"postgresql_inherits": "my_schema.cities"}

Error

Produces two PSQL errors,

NOTICE:  merging column "id" with inherited definition

ERROR:  relation "id" already exists 

SQL state: 42P07

ERROR:  relation "my_schema.cities" does not exist 

SQL state: 42P01

Versions.

• OS: n/a
• Python: 3.13.7
• Alembic: v1.16.5
• SQLAlchemy: v2.0.43
• Database: PSQL v17
• DBAPI: psycopg v3.2.10

Additional context

Thank you so much for a fantastic project! Have a great day!

[zzzeek]

to my knowledge SQLAlchemy has no ability to reflect an INHERITS table with only the columns that are local to this table and this is not a use case I'd expect to work. you can of course manually correct these migrations as needed.

@CaselIT any thoughts?

[CaselIT]

I have never tried them, so it would be surprising if it did work. I'm leaning for at most documenting it in the "what autogenerate cannot do"

[zzzeek]

to my knowledge SQLAlchemy has no ability to reflect an INHERITS table with only the columns that are local to this table and this is not a use case I'd expect to work. you can of course manually correct these migrations as needed.

@CaselIT any thoughts?

oh also, the above was because you showed me a PostgreSQL CREATE TABLE statement, so I thought this was somehow from reflection (which would mean it's a downgrade query).

But also no, on the model side, there's no built in way for that to work, I assume you meant to call that table capitals in your ORM example, that's a new table and all the columns that it gets are from SQLAlchemy's perspective part of that table. PostgreSQL's table inheritance feature presents each inheriting table as what we'd call a "concrete table". so there's no built in translation layer for that. Might be possible to build a translation system that could be plugged in (would definitely be third party) but tricky

[CaselIT]

@zzzeek shall we move this to a discussion or you want to document this limitation?

[zzzeek]

seems there's a few issues here, as far as getting "postgresql_inherits" to render with a schema, we might want to think of a wayt to make that part possible

[zzzeek]

seems there's a few issues here, as far as getting "postgresql_inherits" to render with a schema, we might want to think of a wayt to make that part possible

and that way would be:

from sqlalchemy import quoted_name

class Capitals(Cities):

    # ...

    __table_args__ = {"postgresql_inherits": quoted_name("my_schema.cities", quote=False)}

see https://docs.sqlalchemy.org/en/20/core/sqlelement.html#sqlalchemy.sql.expression.quoted_name

[zzzeek]

OK well one thing we have to fix is the docs at https://docs.sqlalchemy.org/en/20/core/sqlelement.html#sqlalchemy.sql.expression.quoted_name refer to a lot of things that dont exist anymore, but the general idea is correct

[CaselIT]

ok also the docs of inherit in the pg page.

moving it to sqlalchemy as a doc issue