Postgres schemas different from public not supported

[DiddiZ]

Currently, only tables in the public schema are supported. Specifying a different schema results in an error:

from sqlalchemy import Column, MetaData, Table, create_engine
from sqlalchemy.dialects.postgresql import TIMESTAMP
from sqlalchemy.schema import CreateSchema

engine = create_engine("timescaledb+psycopg2://[...]")

table_name = "my_table"
schema_name = "my_schema"

table = Table(
    table_name,
    MetaData(schema=schema_name),
    Column("ts", TIMESTAMP(timezone=True), primary_key=True),
    timescaledb_hypertable={
        "time_column_name": "ts",
        "chunk_time_interval": "1 day",
    },
)


with engine.begin() as conn:
    # Create schema if it does not exist
    if not conn.dialect.has_schema(conn, schema_name):
        conn.execute(CreateSchema(schema_name))

    table.metadata.create_all(conn, checkfirst=True)

Results in:

Traceback (most recent call last):
  File "/home/robin/.local/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 1965, in _exec_single_context
    self.dialect.do_execute(
  File "/home/robin/.local/lib/python3.11/site-packages/sqlalchemy/engine/default.py", line 921, in do_execute
    cursor.execute(statement, parameters)
psycopg2.errors.UndefinedTable: FEHLER:  Relation »my_table« existiert nicht
LINE 3:                 'my_table',
                        ^


The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/home/robin/aix-database-tools/test2.py", line 26, in <module>
    table.metadata.create_all(conn, checkfirst=True)
  File "/home/robin/.local/lib/python3.11/site-packages/sqlalchemy/sql/schema.py", line 5792, in create_all
    bind._run_ddl_visitor(
  File "/home/robin/.local/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 2443, in _run_ddl_visitor
    visitorcallable(self.dialect, self, **kwargs).traverse_single(element)
  File "/home/robin/.local/lib/python3.11/site-packages/sqlalchemy/sql/visitors.py", line 670, in traverse_single
    return meth(obj, **kw)
           ^^^^^^^^^^^^^^^
  File "/home/robin/.local/lib/python3.11/site-packages/sqlalchemy/sql/ddl.py", line 922, in visit_metadata
    self.traverse_single(
  File "/home/robin/.local/lib/python3.11/site-packages/sqlalchemy/sql/visitors.py", line 670, in traverse_single
    return meth(obj, **kw)
           ^^^^^^^^^^^^^^^
  File "/home/robin/.local/lib/python3.11/site-packages/sqlalchemy/sql/ddl.py", line 942, in visit_table
    with self.with_ddl_events(
  File "/usr/lib/python3.11/contextlib.py", line 144, in __exit__
    next(self.gen)
  File "/home/robin/.local/lib/python3.11/site-packages/sqlalchemy/sql/ddl.py", line 830, in with_ddl_events
    target.dispatch.after_create(
  File "/home/robin/.local/lib/python3.11/site-packages/sqlalchemy/event/attr.py", line 487, in __call__
    fn(*args, **kw)
  File "/home/robin/.local/lib/python3.11/site-packages/sqlalchemy/sql/ddl.py", line 320, in __call__
    self.against(target)._invoke_with(bind)
  File "/home/robin/.local/lib/python3.11/site-packages/sqlalchemy/sql/ddl.py", line 315, in _invoke_with
    return bind.execute(self)
           ^^^^^^^^^^^^^^^^^^
  File "/home/robin/.local/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 1412, in execute
    return meth(
           ^^^^^
  File "/home/robin/.local/lib/python3.11/site-packages/sqlalchemy/sql/ddl.py", line 181, in _execute_on_connection
    return connection._execute_ddl(
           ^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/robin/.local/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 1524, in _execute_ddl
    ret = self._execute_context(
          ^^^^^^^^^^^^^^^^^^^^^^
  File "/home/robin/.local/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 1844, in _execute_context
    return self._exec_single_context(
           ^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/robin/.local/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 1984, in _exec_single_context
    self._handle_dbapi_exception(
  File "/home/robin/.local/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 2339, in _handle_dbapi_exception
    raise sqlalchemy_exception.with_traceback(exc_info[2]) from e
  File "/home/robin/.local/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 1965, in _exec_single_context
    self.dialect.do_execute(
  File "/home/robin/.local/lib/python3.11/site-packages/sqlalchemy/engine/default.py", line 921, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.ProgrammingError: (psycopg2.errors.UndefinedTable) FEHLER:  Relation »my_table« existiert nicht
LINE 3:                 'my_table',
                        ^

[SQL: 
            SELECT create_hypertable(
                'my_table',
                'ts',
                chunk_time_interval => INTERVAL '1 day',
                if_not_exists => TRUE
            );
            ]
(Background on this error at: https://sqlalche.me/e/20/f405)

It complains about the table not existing, because it only looks in the public schema. The SQL command should be create_hypertable('my_schema.my_table', 'ts', ...

[dorosch]

Thank you @DiddiZ for your comment. It is indeed possible to add such functionality. I'll fix it soon.

[DiddiZ]

I provided a way to implement this some time ago, is there any update on this?