Handling Redshift identity columns in SQLAlchemy

Question

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

create table if not exists companies (
    id bigint identity primary key,
    name varchar(1024) not null
);

On the SQLAlchemy side I have mapped it like so:

Base = declarative_base()
class Company(Base):
    __tablename__ = 'companies'
    id = Column(BigInteger, primary_key=True)
    name = Column(String)

If I try to create a company:

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

then I get this error:

sqlalchemy.exc.StatementError: (raised as a result of Query-invoked autoflush; 
consider using a session.no_autoflush block if this flush is occurring prematurely) 
(sqlalchemy.exc.ProgrammingError) (psycopg2.ProgrammingError) 
relation "companies_id_seq" does not exist
[SQL: 'select nextval(\'"companies_id_seq"\')'] 
[SQL: u'INSERT INTO companies (id, name) 
VALUES (%(id)s, %(name)s)'] [parameters: [{'name': 'Acme'}]]

The problem is surely that SQLAlchemy is expecting an auto-incrementing sequence - standard technique with Postgres and other conventional DBs. But Redshift doesn't have sequences, instead it offers "identity columns" for auto-generated unique values (not necessarily sequential). Any advice on how to make this work? To be clear, I don't care about auto-incrementing, just need unique primary key values.

Answer 1

Just like you said, Redshift doesn't support sequences so you can remove this part:

select nextval(\'"companies_id_seq"\')

And your insert statement should simply be:

INSERT INTO companies 
(name)
VALUES
('Acme')

In your table, you will see that 'Acme' has a id column with a unique value. You can't insert a value into the id column so you don't specify it in the insert statement. It will be auto populated.

Here is more explanation:

http://docs.aws.amazon.com/redshift/latest/dg/c_Examples_of_INSERT_30.html