sqlalchemy-redshift: autoincrement=True Column() kwarg does not work

# SQLAlchemy==1.0.10, sqlalchemy-redshift==0.4.0, psycopg2==2.6
import sqlalchemy as sa
from sqlalchemy.schema import CreateTable

engine = sa.create_engine('redshift+psycopg2://example')
user = sa.Table(
    'user', 
    sa.MetaData(), 
    # http://docs.sqlalchemy.org/en/latest/core/metadata.html#sqlalchemy.schema.Column.params.autoincrement
    sa.Column('id', sa.Integer, primary_key=True, autoincrement=True),
)

col = user.columns.get('id')
print(col.expression.autoincrement)
# >>> True

print(CreateTable(user).compile(engine))
# CREATE TABLE user (
#         id INTERGER NOT NULL
#         PRIMARY KEY (id)
# )

The ddl_compiler.get_column_default_string() method comes back None. I’m unable to figure out whether this is a lack of documentation and there is an easy solution or a deeper problem with the DDL for redshift.

Thanks in advance!

About this issue

Original URL
State: open
Created 9 years ago
Comments: 23 (10 by maintainers)

Most upvoted comments

Just got this working by specifying redshift_identity=(0, 1) as a kwarg in the sa.Column definition, which is the new syntax (replacing info={'identity': (0, 1)}), in SQLAlchemy > 1.3.

fzembow on Oct 23, 2019

Somehow related to this, I have a problem with a column I want to be auto-incremented, when I don’t specify its value:

import sqlalchemy as sa
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String

Base = declarative_base()

class MyTable(Base):
    __tablename__ = "mytable"
    _id = Column(Integer, primary_key=True, info={"identity":(0,1)}) 
    name = Column(String(50))


mytable = MyTable().__table__
engine = sa.create_engine("redshift+psycopg2://username:password@host")
session = sessionmaker(bind=engine)()
mytable.create(bind=engine)
new_element = MyTable(name="hello")
session.add(new_element)
session.commit() #raises StatementError

The last command raises a StatementError:

StatementError: (sqlalchemy.exc.ProgrammingError) (psycopg2.ProgrammingError) relation "mytable__id_seq" does not exist
 [SQL: 'select nextval(\'"mytable__id_seq"\')'] [SQL: 'INSERT INTO mytable (_id) VALUES (%(_id)s)'] [parameters: [{}]]

The expected results should be if I query the table: [(0, “hello”)]

Can you please tell me how I can auto-increment a Column when the value is not specified ?

EDIT: My question is related to this SO post. Apparently, there is no way to answer my question since redshift doesn’t support sequence… ?

A possible answer has been layed out here, related to #48

qldocao on Feb 18, 2016

I solve it myself. I just remove id column like following.

class Department(Base):
  __tablename__ = 'departments'  
  name = Column(String(256), nullable=False)

soseki-angfa on Nov 21, 2023

There’s no way to use autoincrement ID and get back the last inserted ID.

You have to use a natural key or some other pre-computed identifier

graingert on Jun 7, 2018

This should be closed by #91

jseabold on Aug 24, 2016