postgresqlsqlalchemy

SQLAlchemy syntax for GENERATED ALWAYS AS IDENTITY on postgres database


How could the following table be represented as an SQLA model?

CREATE TABLE x
(
    x_id    integer GENERATED ALWAYS AS IDENTITY,
    x_name  text unique
);

I think that if I create a column using:

    id = Column(Integer, nullable=False, primary_key=True)

The generated SQL won't use GENERATED ALWAYS AS IDENTITY, but will instead use SERIAL.

Completing the following, in order to use the GENERATED ALWAYS AS IDENTITY syntax, is what I'm unsure of:

class X(Base):
    __tablename__ = "x"
    x_id = Column( <something here> ) 
    x_name = Column(Text, unique = True)

Solution

  • You can use an Identity in your column definition.

    class X(Base):
        __tablename__ = "x"
        x_id = Column(Integer, Identity(always=True), primary_key=True)
        x_name = Column(Text, unique=True)
    

    alternatively, using SQLAlchemy 2.0 syntax

    class X(Base):
        __tablename__ = "x"
        x_id: Mapped[int] = mapped_column(Identity(always=True), primary_key=True)
        x_name: Mapped[str] = mapped_column(unique=True)
    

    PS. no need to set nullable=False if you set primary_key=True.