Menu
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?
db.query("CREATE TABLE test_table1 (time TIMESTAMP NOT NULL, name CHAR(100) NOT NULL")
db.query("SELECT create_hypertable('test_table1', 'time')")
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?
710
create_hypertable() Creates a TimescaleDB hypertable from a PostgreSQL table (replacing the latter), partitioned on time and with the option to partition on one or more other columns. The PostgreSQL table cannot be an already partitioned table (declarative partitioning or inheritance).
To create a hypertable, you need to create a standard PostgreSQL table, and then convert it into a TimescaleDB hypertable. Hypertables are intended for time-series data, so your table needs a column that holds time values. This can be a timestamp, date, or integer.
Hypertables are PostgreSQL tables with special features that make it easy to handle time-series data. Anything you can do with regular PostgreSQL tables, you can do with hypertables. In addition, you get the benefits of improved performance and user experience for time-series data. Learn about hypertables.
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.
73
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.
34
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
