Add postgresql tablespace option on indexpr/233

postgresql_tablespace option was already supported on table but not on
index.

2 files changed, 56 insertions, 1 deletions

diff --git a/lib/sqlalchemy/dialects/postgresql/base.py b/lib/sqlalchemy/dialects/postgresql/base.py
index b16a82e04..b76faadca 100644
--- a/lib/sqlalchemy/dialects/postgresql/base.py
+++ b/lib/sqlalchemy/dialects/postgresql/base.py
@@ -415,6 +415,12 @@ keyword argument::
 
 .. versionadded:: 1.0.6
 
+PostgreSQL allows to define the tablespace in which to create the index.
+The tablespace can be specified on :class:`.Index` using the ``postgresql_tablespace``
+keyword argument::
+
+    Index('my_index', my_table.c.data, postgresql_tablespace='my_tablespace')
+
 .. _postgresql_index_concurrently:
 
 Indexes with CONCURRENTLY
@@ -1295,6 +1301,11 @@ class PGDDLCompiler(compiler.DDLCompiler):
                 ['%s = %s' % storage_parameter
                  for storage_parameter in withclause.items()]))
 
+        tablespace_name = index.dialect_options['postgresql']['tablespace']
+
+        if tablespace_name:
+            text += " TABLESPACE %s" % preparer.quote(tablespace_name)
+
         whereclause = index.dialect_options["postgresql"]["where"]
 
         if whereclause is not None:
@@ -1632,7 +1643,8 @@ class PGDialect(default.DefaultDialect):
             "where": None,
             "ops": {},
             "concurrently": False,
-            "with": {}
+            "with": {},
+            "tablespace": None
         }),
         (schema.Table, {
             "ignore_search_path": False,
diff --git a/test/dialect/postgresql/test_compiler.py b/test/dialect/postgresql/test_compiler.py
index 87e48d3f2..c20e48b01 100644
--- a/test/dialect/postgresql/test_compiler.py
+++ b/test/dialect/postgresql/test_compiler.py
@@ -412,6 +412,49 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL):
                             'USING gist (data) '
                             'WITH (buffering = off)')
 
+    def test_create_index_with_tablespace(self):
+        m = MetaData()
+        tbl = Table('testtbl', m, Column('data', String))
+
+        idx1 = Index('test_idx1', tbl.c.data)
+        idx2 = Index('test_idx2', tbl.c.data, postgresql_tablespace='sometablespace')
+        idx3 = Index('test_idx3', tbl.c.data, postgresql_tablespace='another table space')
+
+        self.assert_compile(schema.CreateIndex(idx1),
+                            'CREATE INDEX test_idx1 ON testtbl '
+                            '(data)',
+                            dialect=postgresql.dialect())
+        self.assert_compile(schema.CreateIndex(idx2),
+                            'CREATE INDEX test_idx2 ON testtbl '
+                            '(data) '
+                            'TABLESPACE sometablespace',
+                            dialect=postgresql.dialect())
+        self.assert_compile(schema.CreateIndex(idx3),
+                            'CREATE INDEX test_idx3 ON testtbl '
+                            '(data) '
+                            'TABLESPACE "another table space"',
+                            dialect=postgresql.dialect())
+
+    def test_create_index_with_multiple_options(self):
+        m = MetaData()
+        tbl = Table('testtbl', m, Column('data', String))
+
+        idx1 = Index(
+                'test_idx1',
+                tbl.c.data,
+                postgresql_using='btree',
+                postgresql_tablespace='atablespace',
+                postgresql_with={"fillfactor": 60},
+                postgresql_where=and_(tbl.c.data > 5, tbl.c.data < 10))
+
+        self.assert_compile(schema.CreateIndex(idx1),
+                            'CREATE INDEX test_idx1 ON testtbl '
+                            'USING btree (data) '
+                            'WITH (fillfactor = 60) '
+                            'TABLESPACE atablespace '
+                            'WHERE data > 5 AND data < 10',
+                            dialect=postgresql.dialect())
+
     def test_create_index_expr_gets_parens(self):
         m = MetaData()
         tbl = Table('testtbl', m, Column('x', Integer), Column('y', Integer))