How to determine if a table has Timescale enabled and on which columns?

user_338249 0
Viewed 5836

I have a Timescale database that someone else created.

  1. How do I determine if create_hypertable was called on a table ?

For now I use this, but there must be a better way:

SELECT * FROM hypertable_relation_size('public.data');
  1. And if create_hypertable was called on a table, which parameters (including chunk_time_interval) were used when calling create_hypertable ?

(In some cases there is a from_date and to_date)

timescaledb postgresql
2 Answers

TimescaleDB maintains metadata about hypertables and provides views to query for the metadata. Views are located in schema timescaledb_information and information about hypertables can be retrieved from timescaledb_information.hypertable.

For example:

SELECT * FROM timescaledb_information.hypertable WHERE table_name = 'data';

This API doc contains more information and examples.

Note that the time chunk interval can be changed over time, so the view doesn't provide information about it. So it is necessary to inspect every chunk to see its interval. This can be done by calling function chunk_relation_size_pretty described in the doc here. For example:

SELECT chunk_table, partitioning_columns, ranges 
  FROM chunk_relation_size_pretty('data');

If you are in another schema, then it is necessary to specify fully qualified name of the hypertable as it expects an identifier:

SET SCHEMA 'test';
SELECT chunk_table, partitioning_columns, ranges 
  FROM public.chunk_relation_size_pretty('public.data');
user_8403400
user_131953460
Related