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

Question

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');

2. 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)

Massorete

Answer 1

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.hypertables.

For example:

SELECT * FROM timescaledb_information.hypertables WHERE hypertable_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');

Plossl

Thanks. Just as a comment, the second command need to be the fully qualified table name. So it should be 'schemaname.data'. So chunk_relation_size_pretty('schemaname.data') – Massorete

It works if you are in the same schema. The example in the question uses public schema, so I assume you are in it. I agree it is good to fully qualify in general. In the case of the answer it will require to add a condition in the first answer. – Plossl

Correcting my comment that the condition is not require in the first answer, since it still will find the hypertable by table name. – Plossl

Answer 2

Updated syntax (version>2.0) to get metadata about hypertables :

SELECT * FROM timescaledb_information.hypertables WHERE hypertable_name = 'data';

See Can't display hypertable information: timescaledb_information.hypertable does not exist

Incapable