How do I select a PostgreSQL system column using SQLAlchemy?

Question

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 1

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.