ExcludeConstraint fails to quote column names when necessary.

Issue #3899 resolved
Chris Withers
created an issue

Given this model:

class MyModel(Base):
    __tablename__ = 'my_model'
    id = Column(Integer, primary_key=True)
    period = Column(TSRANGE())
    group = Column(String)
    __table_args__ = [ExcludeConstraint(('period', '&&'), ('group', '='))]

create_all will barf with a syntax error at or near "group"

Comments (9)

  1. Michael Bayer repo owner

    why string name? why not (also fixed the args format):

    class MyModel(Base):
        __tablename__ = 'my_model'
        id = Column(Integer, primary_key=True)
        period = Column(TSRANGE())
        group = Column(String)
    
    #    __table_args__ = (ExcludeConstraint(('period', '&&'), ('group', '=')), )
    
        __table_args__ = (ExcludeConstraint((period, '&&'), (group, '=')), )
    

    if you put a string here I don't know that this is giving SQLAlchemy enough context to know this is a single column name that is quotable.

  2. Michael Bayer repo owner

    derp, nope, our docstring is wrong, that string can be any SQL expression:

    "The EXCLUDE clause defines an exclusion constraint, which guarantees that if any two rows are compared on the specified column(s) or expression(s) using the specified operator(s),"

    and this is accepted:

    class MyModel(Base):
        __tablename__ = 'my_model'
        id = Column(Integer, primary_key=True)
        period = Column(TSRANGE())
        group = Column(String)
    
        __table_args__ = (ExcludeConstraint((period, '&&'), (func.lower(group), '=')), )
    
  3. Michael Bayer repo owner

    e.g.

    CREATE TABLE my_model (
        id SERIAL NOT NULL, 
        period TSRANGE, 
        "group" VARCHAR, 
        PRIMARY KEY (id), 
        EXCLUDE USING gist (period WITH &&, lower("group") WITH =)
    )
    

    fails for me for "gist" reasons but not syntactically.

  4. Log in to comment