postgresql - 将数据插入 postgres 表时如何处理声明性模型中生成的列？

Question

如果postgres中的表如下:

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)

如何正确处理生成的 height_in列是sqlalchemy？
这个想法是只插入 id , email和 height_cm但是通过指定列 height_in 使用 sqlalchemy sqlalchemy 自动将 NULL 插入 height_in当在表中插入一行时 - 和 postgres 然后出错，因为这是不允许的。

Answer 1

将该列声明为 Computed :

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="foo@bar.baz", height_cm=175)
    session.add(foo)
    session.commit()
    print(foo)
    # <User(id=1, email='foo@bar.baz', height_cm=175, height_in=68.8976377952755906)>