如何在向Postgres表插入数据时处理声明模型中生成的列？

Question

如果在PostgreSQL中有以下表格：

CREATE TABLE user (
    id integer PRIMARY KEY,
    email text UNIQUE,
    height_cm numeric,
    height_in numeric GENERATED ALWAYS AS (height_cm / 2.54) STORED
);

以下是sqlalchemy模型：

from sqlalchemy.ext.declarative import declarative_base

class User(declarative_base()):
    __tablename__ = "user"

    id = Column(Integer, primary_key=True)
    email = Column(String, unique=True, index=True, nullable=False)
    height_cm = Column(Numeric)
    height_in = Column(Numeric)

如何正确处理sqlalchemy生成的“height_in”列？

思路是仅使用sqlalchemy插入“id”，“email”和“height_cm”列，但是指定“height_in”列时，sqlalchemy会自动在将行插入表时将NULL插入“height_in”列，因此postgres会出现错误，因为不允许这样做。

- Greg

---

注意：GENERATED ALWAYS AS ... STORED 语法似乎是在 Postgresql v12 中引入的。 - undefined

Answer 1

将该列声明为计算列：

class User(Base):
    __tablename__ = "user"

    id = Column(Integer, primary_key=True)
    email = Column(String, unique=True, index=True, nullable=False)
    height_cm = Column(Numeric)
    height_in = Column(Numeric, Computed("height_cm / 2.54"))

    def __repr__(self):
        return (
            f"<User(id={self.id}, email='{self.email}', "
            f"height_cm={self.height_cm}, height_in={self.height_in})>"
        )


Base.metadata.drop_all(engine)
Base.metadata.create_all(engine)
"""DDL generated:
CREATE TABLE "user" (
    id SERIAL NOT NULL, 
    email VARCHAR NOT NULL, 
    height_cm NUMERIC, 
    height_in NUMERIC GENERATED ALWAYS AS (height_cm / 2.54) STORED, 
    PRIMARY KEY (id)
)
"""

with Session(engine) as session:
    foo = User(email="[e-mail]", height_cm=175)
    session.add(foo)
    session.commit()
    print(foo)
    # <User(id=1, email='[e-mail]', height_cm=175, height_in=68.8976377952755906)>

- Gord Thompson