Can't create table with boolean column using SQLAlchemy on Redshift

Question

After a succesfull connection to Redshift, I'm trying to ensure my tables exist in the cluster. This is the code I'm running:

from sqlalchemy.engine import URL, create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Boolean, SMALLINT, Column


Base = declarative_base()


class Table(Base):
    __tablename__ = "a_table"

    id = Column(SMALLINT, primary_key=True)
    a_boolean_column = Column(Boolean)


# example values
engine = create_engine(
    URL.create(
        drivername="postgresql+psycopg2",
        username="username",
        password="password",
        host="127.0.0.1",
        port=5439,
        database="dev",
    )
)

base.metadata.create_all(engine)

I'm getting the following error:

sqlalchemy.exc.ProgrammingError: (psycopg2.errors.SyntaxError) syntax error at or near "a_boolean_column"
LINE 12:  a_boolean_column BOOLEAN, 
          ^

[SQL: 
CREATE TABLE a_table (
        id INTEGER NOT NULL, 
        a_boolean_column BOOLEAN, 
        PRIMARY KEY (id),
)

]

I've tried using the BOOLEAN data type from sqlalchemy, and tried setting a default value, but both failed.

Versions:

SQLAlchemy==1.4.47
sqlalchemy-redshift==0.8.13

Answer 1

We have to force sqlalchemy to use the correct engine using the drivername argument of URL.create:

# example values
engine = create_engine(
    URL.create(
        drivername="redshift+psycopg2",
        username="username",
        password="password",
        host="127.0.0.1",
        port=5439,
        database="dev",
    )
)

Before it was drivername="postgresql+psycopg2", when it should have been drivername="redshift+psycopg2".