Helper Timescale

SELECT name, setting, source FROM pg_settings WHERE name = 'TimeZone';

SELECT pid FROM pg_stat_activity

SELECT pg_terminate_backend(<pid>);

To retrieve all table information from a TimescaleDB database, which is built on top of PostgreSQL, you can query the system catalog tables. Specifically, TimescaleDB adds metadata for hypertables and continuous aggregates, which you may also want to include.

Here are some useful SQL queries to get various types of table information:

1. List all tables (regular and hypertables)

sql
SELECT table_schema, table_name
FROM information_schema.tables
WHERE table_type = 'BASE TABLE'
AND table_schema NOT IN ('information_schema', 'pg_catalog')
ORDER BY table_schema, table_name;

2. List all hypertables (Timescale-specific tables)

sql
SELECT hypertable_schema, hypertable_name
FROM timescaledb_information.hypertables
ORDER BY hypertable_schema, hypertable_name;

3. Get detailed information about columns for all tables

sql
SELECT table_schema, table_name, column_name, data_type, is_nullable, column_default
FROM information_schema.columns
WHERE table_schema NOT IN ('information_schema', 'pg_catalog')
ORDER BY table_schema, table_name, ordinal_position;

4. List all continuous aggregates (Timescale-specific)

sql
SELECT view_schema, view_name, materialization_hypertable
FROM timescaledb_information.continuous_aggregates
ORDER BY view_schema, view_name;

5. Get size information of tables and hypertables

sql
SELECT
    table_schema,
    table_name,
    pg_size_pretty(pg_total_relation_size(table_schema || '.' || table_name)) AS total_size
FROM information_schema.tables
WHERE table_type = 'BASE TABLE'
AND table_schema NOT IN ('information_schema', 'pg_catalog')
ORDER BY pg_total_relation_size(table_schema || '.' || table_name) DESC;

6. Get chunk information for hypertables

sql
SELECT hypertable_name, chunk_name, table_schema, table_name, range_start, range_end
FROM timescaledb_information.chunks
ORDER BY hypertable_name, chunk_name;

7. Get constraints and indexes for all tables

sql
SELECT 
    tc.table_schema, 
    tc.table_name, 
    tc.constraint_name, 
    tc.constraint_type
FROM information_schema.table_constraints tc
WHERE tc.table_schema NOT IN ('information_schema', 'pg_catalog')
ORDER BY tc.table_schema, tc.table_name, tc.constraint_type;