如何使用SQLAlchemy选择PostgreSQL系统列？

Question

Postgresql在每个表上隐式定义了几个列，如xmax和ctid(见文档)。

假设我的SQLAlchemy表定义没有指定这些列，是否有一种方法可以使用核心SQL功能(即不是SA的ORM部分)来选择它们?

以下方法不起作用，因为xmax在表定义中没有显式定义。

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)

我的具体需求是在upsert语句的returning子句中引用xmax。

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

- Rob Cowie

---

我现在记得我可以提供字面的 SQL 字符串来规避这个问题，尽管我对任何其他方法都很感兴趣。insert(mytable).returning(sa.text("xmax = 0 AS inserted")) - Rob Cowie

Answer 1

如果您不想更改现有的表声明，可以使用sqlalchemy.column()函数（请注意 column 中小写的字母 c）：

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

然而，如果你的SQL语句从多个表中进行选择（例如在连接操作中），那么PostgreSQL会抱怨它不知道你在谈论哪个xmax列：

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

在这种情况下，您可以使用（不幸的是未记录的）_selectable参数：

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

这在涉及联表查询时与仅从一个表中进行选择的情况一样有效，因此您始终可以使用它。

- LeoRochael

Answer 2

一种选择是在您的SA表定义中将xmax声明为系统列：

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

这将允许您像任何其他列一样访问table.c.xmax (因此您提出的table.c.xmax == 0应该会起作用)。 system=True标志告诉SA在发出CREATE TABLE时不要尝试显式创建xmax列。

（我在这里使用了sa.TEXT作为解决方法，因为sqlalchemy.dialects.postgresql没有提供XID数据类型，并且显然xid无法转换为数字类型。）

- Anthony Carapetis

---

谢谢，这是一个有趣的答案。我不知道在列上有系统标志。 - Rob Cowie