0

I have a model in my flask project:

class Location(db.Model):
    __tablename__ = 'location'

    id = db.Column(db.Integer, primary_key=True)
    longitude = db.Column(db.Float, nullable=False)
    latitude = db.Column(db.Float, nullable=False)
    address_1 = db.Column(db.String(50))
    address_2 = db.Column(db.String(8))
    country_id = db.Column(db.Integer, db.ForeignKey('country.id', ondelete='SET NULL'))
    city_id = db.Column(db.Integer, db.ForeignKey('city.id', ondelete='SET NULL'))

I need to ensure uniqueness of location via applying unique constraint on all columns except an id. I've tried this one:

__table_args__ = (
        db.UniqueConstraint(
            longitude,
            latitude,
            address_1,
            address_2,
            country_id,
            city_id,
            name="unique_loc"),
    )

... but I still can save duplicates because of nullable `address_1', 'address_2' (in more details). I've investigated a bit and found a possible solution:

CREATE UNIQUE INDEX unique_loc ON location
(longitude, latitude, COALESCE(address_1, ''), COALESCE(address_2, ''), country_id, city_id);

Is it possible to write this constraint either via ORM or raw query? The database I use - postgres 13. Any help would be appreciated.

jeldzinski
  • 37
  • 7

2 Answers2

1

You can use Index (with unique=True option) and func.coalesce:

   from sqlalchemy import func
   ...   

   __table_args__ = (
        db.Index(
            "unique_loc",
            longitude,
            latitude,
            func.coalesce(address_1, ''),
            func.coalesce(address_2, ''),
            country_id,
            city_id,
            unique=True
        ),
    )
jorzel
  • 1,216
  • 1
  • 8
  • 12
  • I've ran `flask db migrate` and got an error `UserWarning: autogenerate skipping functional index unique_loc; not supported by SQLAlchemy reflection`. However, I've found solution with raw query thanks to your response. Thanks for your help. – jeldzinski Jul 07 '22 at 06:10
0

So, I've found out how to create an index with raw query. All you need is generate new migration with flask db migrate and modify both def upgrade() and 'def downgrade`:

def upgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    ...
    op.execute(f"CREATE UNIQUE INDEX unique_loc ON location "
               f"(longitude, latitude, COALESCE(address_1, '-1'), "
               f"COALESCE(address_2, '-1'), country_id, city_id);")
    ...
    # ### end Alembic commands ###

def downgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    ...
    op.drop_index('unique_loc', table_name='location')
    ...
    # ### end Alembic commands ###
jeldzinski
  • 37
  • 7