I am creating a table using Flask SQL Alchemy as follows:
class Price(db.Model):
__tablename__ = "prices"
id = db.Column(db.Integer, primary_key=True)
country_code = db.Column(db.String(2), nullable=False)
value = db.Column(db.Float(precision=2), nullable=False)
start = db.Column(db.DateTime(timezone=True), nullable=False)
end = db.Column(db.DateTime(timezone=True), nullable=False)
price_type = db.Column(db.String(32), nullable=False)
and then I want to create the table and convert it into hypertable by doing:
def register_timescale():
result = db.session.execute(
"select exists(select * from timescaledb_information.hypertable " +
" where table_name='prices')")
is_hypertable = [row[0] for row in result]
if is_hypertable[0] is False:
db.session.execute(
"SELECT create_hypertable('prices', 'start', chunk_time_interval=>INTERVAL '1 day')")
db.session.commit()
def create_app(app_config):
app = Flask(__name__)
app.config.from_object(app_config)
register_extensions(app)
register_blueprints(app)
with app.app_context():
db.create_all()
register_timescale()
return app
I want to create a hypertable of the table prices
with the start
column being time_column_name
as described at create_hypertable and the chunk_time_interval
should be 1 day. Meaning, one chunk must have data of 1 day
Example:
One chunk must contain data where start
value is 2020-04-19 00:00:00+02
and end
value is 2020-04-20 00:00:00+02
(from row 1 to 24 in the below image) and then the second chunk must contain value from 2020-04-20 00:00:00+02
till 2020-04-21 00:00:00+02
(from row 25 to 48 in the below image) and so on.
When I do this, I am getting the following error:
Exception has occurred: DatabaseError (psycopg2.DatabaseError) cannot
create a unique index without the column "start" (used in
partitioning)[SQL: SELECT create_hypertable('prices', 'start',
chunk_time_interval=>INTERVAL '1 day')] (Background on this error at:
http://sqlalche.me/e/4xp6)
Then, I tried changing the register_timescale()
function as follows:
def register_timescale():
result = db.session.execute(
"select exists(select * from timescaledb_information.hypertable " +
" where table_name='prices')")
is_hypertable = [row[0] for row in result]
if is_hypertable[0] is False:
db.session.execute(
"ALTER DATABASE mydb SET timezone TO 'Europe/Berlin'")
db.session.execute("SELECT pg_reload_conf()")
db.session.execute("ALTER TABLE prices ADD PRIMARY KEY (id, start)")
db.session.execute(
"SELECT create_hypertable('prices', (id, start), chunk_time_interval=>INTERVAL '1 day')")
db.session.commit()
And this gives me the following error:
Exception has occurred: ProgrammingError
(psycopg2.errors.InvalidTableDefinition) multiple primary keys for
table "prices" are not allowed[SQL: ALTER TABLE prices ADD PRIMARY KEY (id, start)] (Background on
this error at: http://sqlalche.me/e/f405)
My data looks as follows
Can someone please point out the mistake I am doing?
Thanks
Best Answer
TimescaleDB requires that unique and primary keys include the time dimension column. While only one column can be defined as the time dimension. You can read about limitations in create_hypertable doc.
Since Flask SQL Alchemy requires a primary key, you can define a compound primary key on the time dimension column, which is
start
in your case, andid
. Based on this doc I believe it will be:Then you should be able to execute
create_hypertable
statement successfully. It should include only columnstart
as the time dimension, i.e.:Note that
create_hypertable
will also create the default index onstart
. Depending on your application queries you might not need this index and the primary key might be enough. In such case you can drop the index onstart
either by setting optioncreate_default_indexes
tofalse
during the call to create hypertable or by executingDROP INDEX
statement afterwards. The best practice section contains more information.