How do I select a PostgreSQL system column using SQLAlchemy?

Postgresql implicitly defines several columns on every table, such as xmax and ctid (see docs).

Assuming my SQLALchemy table definition does not specify these columns, is there a way to select them using the core sql functionality (i.e. not the ORM part of SA)?

The following does not work as xmax is not explicitly defined in the table definition.

table = sa.Table(
    "mytable",
    metadata,
    sa.Column("col_a", sa.BIGINT),
    sa.Column("date", sa.DATE),
)

s = sa.select([table.c.xmax])
result = engine.execute(s)

Specifically my requirement is to reference xmax in the returning clause of an upsert.

insert(mytable).returning((mytable.c.xmax == 0).label("inserted"))

Answer

One option is to declare xmax in your SA table definition as a system column:

table = sa.Table(
    # ...,
    sa.Column("xmax", sa.TEXT, system=True),
)

This will allow you to access table.c.xmax like any other column (so your proposed table.c.xmax == 0 should then work). The system=True flag tells SA not to attempt to create the xmax column explicitly when emitting CREATE TABLE.

(I’ve used sa.TEXT as a bit of a workaround here because sqlalchemy.dialects.postgresql doesn’t provide an XID datatype, and apparently xid cannot be casted to a numeric type.)