1-- This file and its contents are licensed under the Apache License 2.0. 2-- Please see the included NOTICE for copyright information and 3-- LICENSE-APACHE for a copy of the license. 4 5CREATE TYPE custom_type_for_compression AS (high int, low int); 6 7CREATE TABLE compress ( 8 time TIMESTAMPTZ NOT NULL, 9 small_cardinality TEXT NULL, 10 large_cardinality TEXT NULL, 11 some_double DOUBLE PRECISION NULL, 12 some_int integer NULL, 13 some_custom custom_type_for_compression NULL, 14 some_bool boolean NULL 15 ); 16 17SELECT table_name FROM create_hypertable( 'compress', 'time'); 18 19INSERT INTO compress 20SELECT g, 'POR', g::text, 75.0, 40, (1,2)::custom_type_for_compression, true 21FROM generate_series('2018-12-01 00:00'::timestamp, '2018-12-31 00:00'::timestamp, '1 day') g; 22 23INSERT INTO compress 24SELECT g, 'POR', NULL, NULL, NULL, NULL, NULL 25FROM generate_series('2018-11-01 00:00'::timestamp, '2018-12-31 00:00'::timestamp, '1 day') g; 26 27INSERT INTO compress 28SELECT g, 'POR', g::text, 94.0, 45, (3,4)::custom_type_for_compression, true 29FROM generate_series('2018-11-01 00:00'::timestamp, '2018-12-15 00:00'::timestamp, '1 day') g; 30 31ALTER TABLE compress SET (timescaledb.compress, timescaledb.compress_segmentby='small_cardinality'); 32 33SELECT compress_chunk(chunk.schema_name|| '.' || chunk.table_name) as count_compressed 34FROM _timescaledb_catalog.chunk chunk 35INNER JOIN _timescaledb_catalog.hypertable hypertable ON (chunk.hypertable_id = hypertable.id) 36WHERE hypertable.table_name = 'compress' and chunk.compressed_chunk_id IS NULL 37ORDER BY chunk.id; 38 39\if :WITH_ROLES 40GRANT SELECT ON compress TO tsdbadmin; 41\endif 42