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"))

If you don't want to change your existing table declarations, you can use the sqlalchemy.column() function (note the lowercase c in column):

xmax = sa.column('xmax')
sa.insert(mytable).returning((xmax == 0).label("inserted"))

However, if your SQL statement selects from more than one table (e.g. in a join) then PostgreSQL will complain it doesn't know which xmax column you're talking about:

ProgrammingError: (psycopg2.ProgrammingError) column "xmax" does not exist

In this case you can use the (unfortunately undocumented) _selectable parameter:

xmax = sa.column('xmax', _selectable=mytable)
sa.insert(mytable).returning((xmax == 0).label("inserted"))

Which works in a joined tables query just as well as in the case where you're selecting only from one table, so you can always use it if you want.

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.)