How to handle generated columns in the declarative model when inserting data into a postgres table?

user_4605629 0
Viewed 119

If the table in postgres is as follows:

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

And the sqlalchemy model is:

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)

How to correctly deal with the generated height_in column is sqlalchemy?

The idea is to only insert id, email and height_cm using sqlalchemy however by specifying the column height_in sqlalchemy automatically inserts NULL into height_in when inserting a row into the table - and postgres then errors out as this is not allowed.

sqlalchemy postgresql
1 Answers

Declare the column as 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)>
user_21443900
Related