Handling Redshift identity columns in SQLAlchemy

Question

This question is related to a Question posted a few years ago.

I'm using the redshift-sqlalchemy package to connect SQLAlchemy to Redshift. In Redshift I have a simple "companies" table:

CREATE TABLE apis
(
   id   INTEGER IDENTITY(1,1) NOT NULL,
   name   VARCHAR(255) NOT NULL,
   PRIMARY KEY(id)
);

On the SQLAlchemy side I have mapped it like so:

Base = declarative_base()
class Company(Base):
    __tablename__ = 'companies'
    id = Column(BigInteger, primary_key=True, redshift_identity=(1, 1))
    name = Column(String, nullable=False)
    def __init__(self, name: str):
        self.name = name

If I try to create a company:

company = Company(name = 'Hoge')
session.add(company)
session.commit()

then I get this error:

(sqlalchemy.exc.ProgrammingError) (redshift_connector.error.ProgrammingError) {'S': 'ERROR', 'C': '42P01', 'M': 'relation "companies_id_seq" does not exist', 'F': '../src/pg/src/backend/catalog/namespace.c', 'L': '267', 'R': 'LocalRangeVarGetRelid'}
[SQL: INSERT INTO companies (id, name) VALUES (%s, %s)]
[parameters: [{'name': 'Hoge'}]]
(Background on this error at: https://sqlalche.me/e/14/f405)

I think the problem is that you are trying to insert data also in the ID column with the IDENTITY option set.

SQL: INSERT INTO companies (id, name) VALUES (%s, %s)

If I execute SQL directly on a Redshift table I get the following error.

ERROR: cannot set an identity column to a value.

Please tell me how to define the auto-populated ID column in sqlalchemy-redshift model?

Handling Redshift identity columns in SQLAlchemy

0 Answers0