TimescaleDB: Is it possible to call 'create_hypertable' from Python?

Question

I want to create TimescaleDB tables in Postgres on the fly as I'm dealing with data sources that change (financial feeds, so could be 100, could be 1000) over time and I want one table per data source.

I can create the tables no problem from Python, but when I call SELECT create_hypertable(test_table1, time) it throws an error. The same query seems to work fine when executed from pSQL of course, so it looks like the timescale API isn't available via psycopg2 perhaps?

Environment:

• Python 3.6.4
• psycopg2-binary-2.7.4 (installed with the flag: --no-binary :all:)
• Postgres: 10.3
• Timescaledb: 0.8.0-2
• MacOS: 10.13.3

Test Code:

db.query("CREATE TABLE test_table1 (time TIMESTAMP NOT NULL, name CHAR(100) NOT NULL")
db.query("SELECT create_hypertable('test_table1', 'time')")

Error:

2018-03-05 11:45:36,901 [MainThread ] [ERROR] function create_hypertable(unknown, unknown) does not exist
LINE 1: SELECT create_hypertable('temp_table1', 'time')
. . . . . . . . . . . . . . ^
HINT: No function matches the given name and argument types. You might need to add explicit type casts.

Does anyone know if there currently anyway to making this work? Have I missed something simple? Or is there another service that can replace timescale functionality that supports being dynamically created?

Answer 1

That output implies that you haven't installed the TimescaleDB extension on the database you're running create_hypertable on. Make sure you run:

CREATE EXTENSION IF NOT EXISTS timescaledb CASCADE;

on your database before running create_hypertable. To ensure the extension has been created, run the following query:

select * from pg_extension;

psycopg shouldn't have any effect on this as the .query() call appears to just be executing the raw SQL you pass it. Make sure your psycopg client is connected to the same database as the one you initially installed the TimescaleDB extension on.

Answer 2

You might try casting your inputs it looks like it could be an issue with the way inputs are being treated.So something like SELECT create_hypertable('test_table1'::regclass, 'time'::name); might work better.