support postgresql table options

Issue #2051 resolved
Michael Bayer
repo owner created an issue

here's a userland monkeypatch that should be easy to adapt straight into the dialect:

# See:
def patch_postgresql_table_ddl():
  import sqlalchemy.dialects.postgresql.base
  class MyPGDDLCompiler(sqlalchemy.dialects.postgresql.base.PGDDLCompiler):
    def post_create_table(self, table):
      """Build table-level CREATE options like TABLESPACE."""

      table_opts = []

      inherits = table.kwargs.get('postgresql_inherits')
      if inherits is not None:
          if not isinstance(inherits, (list, tuple)):
              inherits = (inherits,)
                  '\nINHERITS ( ' +
                  ', '.join(isinstance(i, basestring) and i
                            or self.process(i)
                            for i
                            in inherits) +
                  ' )')

      on_commit = table.kwargs.get('postgresql_on_commit')
      if on_commit:
              '\nON COMMIT ' +
              on_commit.upper().replace('_', ' '))

      with_oids = table.kwargs.get('postgresql_with_oids')
      if with_oids is not None:
          if with_oids:
              w = 'WITH'
              w = 'WITHOUT'
          table_opts.append('\n%s OIDS' % w)

      tablespace = table.kwargs.get('postgresql_tablespace')
      if tablespace:
          table_opts.append('\nTABLESPACE ' + tablespace)

      return ''.join(table_opts)

  sqlalchemy.dialects.postgresql.base.PGDDLCompiler = MyPGDDLCompiler
  sqlalchemy.dialects.postgresql.base.PGDialect.ddl_compiler = MyPGDDLCompiler

Comments (13)

  1. Thomas Farvour

    Stumbled across this while searching for the ability to add TABLESPACE to the PG table options for DDL. Any way this might end up in the main 0.9 branch? Or possible future release?

  2. Michael Bayer reporter

    we have a modernized system of supporting these keywords, they are easy to provide via pullreq which we can pull right in, here's a start:

    diff --git a/lib/sqlalchemy/dialects/postgresql/ b/lib/sqlalchemy/dialects/postgresql/
    index 42ec19c..62b1a6c 100644
    --- a/lib/sqlalchemy/dialects/postgresql/
    +++ b/lib/sqlalchemy/dialects/postgresql/
    @@ -1266,6 +1266,13 @@ class PGDDLCompiler(compiler.DDLCompiler):
                 colspec += " NOT NULL"
             return colspec
    +    def post_create_table(self, table):
    +        table_opts = []
    +        if "tablespace" in table.dialect_options['postgresql']:
    +            table_opts.append("TABLESPACE %s" % table.dialect_options['postgresql']['tablespace'])
    +        return ' '.join(table_opts)
         def visit_create_enum_type(self, create):
             type_ = create.element
    @@ -1566,7 +1573,8 @@ class PGDialect(default.DefaultDialect):
                 "ops": {}
             (schema.Table, {
    -            "ignore_search_path": False
    +            "ignore_search_path": False,
    +            "tablespace": None
  3. Michael Bayer reporter

    so, build upon that, note that the argument above is like Table(..., postgresql_tablespace='myspace'), then some tests in test/dialect/postgresql/, can go right in.

  4. Thomas Farvour

    By the way the main reason for this feature/request is for seamless integration with the Alembic tool that we're using. It works and is very helpful to hand off these migrations to say, a DBA, that has configured the table and index tablespaces already but would like to avoid hacking the generated .SQL.

  5. Michael Bayer reporter

    yes, extra table arguments come via __table_args__ = {'postgresql_tablespace': 'foo'}.

    and yes you can do it for indexes as well, see the approach in postgresql/ for "using", "where", "ops", etc., including visit_create_index, and the tests in test_compiler.

    My sample patch above has a bug, editing it now.

  6. Thomas Farvour

    Excellent. So ultimately this just involves adding the functionality to the base dialect module for postgres and overriding some methods, according to your patch above?

  7. Log in to comment