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?

Krugersdorp

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.

Upstairs

Ahhhhhh! So this is the solution: I had run CREATE EXTENSION IF NOT EXISTS timescaledb CASCADE; on the database when I created it following the installation steps - but because I have a reset script that drops all the tables and creates them again - I have to run CREATE EXTENSION IF NOT EXISTS timescaledb CASCADE; again to connect the extension again. So when I had it working in pSQL, it was before I ran the reset script and of course, when I tried from Python, it was after. – Krugersdorp

So this knocks out the connection: self.query("DROP SCHEMA public CASCADE", True) | self.query("CREATE SCHEMA public", True) | self.query("GRANT ALL ON SCHEMA public TO postgres", True)| self.query("GRANT ALL ON SCHEMA public TO public", True). Thanks very much for you help, much appreciated! – Krugersdorp

I tried above described method but still getting the same error. SELECT create_hypertable('conditions', 'time'); ERROR: function create_hypertable(unknown, unknown) does not exist LINE 1: SELECT create_hypertable('conditions', 'time'); ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. – Slade

Answer 2

I had the same issue on Postgres: 12, it's resolved in Postgres: 13 by adding the parameter if_not_exists:

SELECT create_hypertable('table_name', 'time', if_not_exists => TRUE);

Vadose

Answer 3

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.

Clarhe

Answer 4

If U're sure that extension is loaded simply do:

db.query("SELECT public.create_hypertable('test_table1', 'time')")

in my case i have sheme "my_scheme" (not public one) and create_hypertable exists for scheme public only

Agentive

Answer 5

As per the official documentation Here, we have to "Extend the database with TimescaleDB", and so, before converting the table to "hypertable", we must execute the below command, to extend.

CREATE EXTENSION IF NOT EXISTS timescaledb CASCADE;

Roti