CREATE TABLE

Create a table or a hypertable

Since 2.20.0

Create a partitioned on a single dimension with enabled, or create a standard relational table.

A is a specialized table that automatically partitions your data by time. All actions that work on a table, work on s. For example, ALTER TABLE and SELECT. By default, a is partitioned on the time dimension. To add secondary dimensions to a , call add_dimension. To convert an existing relational table into a , call create_hypertable.

When you create a using CREATE TABLE … WITH …, the default partitioning column is automatically the first column with a timestamp data type. Also, creates a columnstore policy that automatically converts your data to the , after an interval equal to the value of the chunk_interval, defined through compress_after in the policy. This columnar format enables fast scanning and aggregation, optimizing performance for analytical workloads while also saving significant storage space. In the conversion, s are compressed by up to 98%, and organized for efficient, large-scale queries.

You can customize this policy later using alter_job. However, to change after or created_before, the compression settings, or the the policy is acting on, you must remove the columnstore policy and add a new one.

You can also manually convert s in a to the .

to foreign keys are not allowed, all other combinations are permitted.

The settings are applied on a per- basis. You can change the settings by calling ALTER TABLE without first converting the entire back to the . The new settings apply only to the s that have not yet been converted to , the existing s in the do not change. Similarly, if you remove an existing columnstore policy and then add a new one, the new policy applies only to the unconverted s. This means that s with different settings can co-exist in the same .

calculates default settings for each when it is created. These settings apply to each , and not the entire . To explicitly disable the defaults, set a setting to an empty string.

CREATE TABLE extends the standard CREATE TABLE. This page explains the features and arguments specific to .

For v2.23.0 and higher, the table is automatically partitioned on the first column in the table with a timestamp data type. If multiple columns are suitable candidates as a partitioning column, throws an error and asks for an explicit definition. For earlier versions, set partition_column to a time column.

If you are self-hosting v2.20.0 to v2.22.1, to convert your data to the after a specific time interval, you have to call add_columnstore_policy after you call CREATE TABLE

If you are self-hosting v2.19.3 and below, create a relational table, then convert it using create_hypertable. You then enable with a call to ALTER TABLE.

Samples

Create a hypertable partitioned on the time dimension and enable columnstore

CREATE TABLE crypto_ticks (
   "time" TIMESTAMPTZ,
   symbol TEXT,
   price DOUBLE PRECISION,
   day_volume NUMERIC
) WITH (
  tsdb.hypertable,
  tsdb.segmentby='symbol',
  tsdb.orderby='time DESC'
);

When you create a using CREATE TABLE WITH, automatically creates a columnstore policy that uses the interval as the compression interval, with a default schedule interval of 1 day. The default partitioning column is automatically selected as the first column with a timestamp or timestampz data type.

Create a hypertable partitioned on the time with fewer chunks based on time interval

CREATE TABLE IF NOT EXISTS hypertable_control_chunk_interval(
 time int4 NOT NULL,
 device text,
 value float
) WITH (
 tsdb.hypertable,
 tsdb.chunk_interval=3453
);

Create a hypertable partitioned using UUIDv7

Postgres 17 and lower

 -- UUIDv7 compression is enabled by default
 CREATE TABLE events (
    id  uuid PRIMARY KEY DEFAULT generate_uuidv7(),
    payload jsonb
 ) WITH (tsdb.hypertable, tsdb.partition_column = 'id');

Postgres v18

-- UUIDv7 compression is enabled by default
CREATE TABLE events (
   id  uuid PRIMARY KEY DEFAULT uuidv7(),
   payload jsonb
) WITH (tsdb.hypertable, tsdb.partition_column = 'id');

Enable data compression during ingestion

When you set timescaledb.enable_direct_compress_copy your data gets compressed in memory during ingestion with COPY statements. By writing the compressed batches immediately in the , the IO footprint is significantly lower. Also, the columnstore policy you set is less important, INSERT already produces compressed s.

Please note that this feature is a tech preview and not production-ready. Using this feature could lead to regressed query performance and/or storage ratio, if the ingested batches are not correctly ordered or are of too high cardinality.

To enable in-memory data compression during ingestion:

SET timescaledb.enable_direct_compress_copy=on;

Important facts

• High cardinality use cases do not produce good batches and lead to degreaded query performance.
• The is optimized to store 1000 records per batch, which is the optimal format for ingestion per segment by.
• WAL records are written for the compressed batches rather than the individual tuples.
• Currently only COPY is support, INSERT will eventually follow.
• Best results are achieved for batch ingestion with 1000 records or more, upper boundary is 10.000 records.
• Continous Aggregates are not supported at the moment.

1. Create a :
   CREATE TABLE t(time timestamptz, device text, value float) WITH (tsdb.hypertable);
   
2. Copy data into the : You achieve the highest insert rate using binary format. CSV and text format are also supported.
   COPY t FROM '/tmp/t.binary' WITH (format binary);
   

Create a Postgres relational table

CREATE TABLE IF NOT EXISTS relational_table(
 device text,
 value float
);

Arguments

The syntax is:

CREATE TABLE <table_name> (
   -- Standard Postgres syntax for CREATE TABLE
)
WITH (
   tsdb.hypertable = true | false
   tsdb.partition_column = '<column_name> ',
   tsdb.chunk_interval = '<interval>'
   tsdb.create_default_indexes =  true | false
   tsdb.associated_schema = '<schema_name>',
   tsdb.associated_table_prefix = '<prefix>'
   tsdb.orderby = '<column_name> [ASC | DESC] [ NULLS { FIRST | LAST } ] [, ...]',
   tsdb.segmentby = '<column_name> [, ...]',
   tsdb.sparse_index = '<index>(<column_name>), index(<column_name>)'
)

Name	Type	Default	Required	Description
tsdb.hypertable	BOOLEAN	true	✖	Create a new for time-series data rather than a standard relational table.
tsdb.partition_column	TEXT	The first column in the table with a timestamp data type	✖	Set the time column to automatically partition your time-series data by.
tsdb.chunk_interval	TEXT	7 days	✖	Change this to better suit your needs. For example, if you set chunk_interval to 1 day, each stores data from the same day. Data from different days is stored in different s.
tsdb.create_default_indexes	BOOLEAN	true	✖	Set to false to not automatically create indexes. The default indexes are: On all s, a descending index on partition_column On s with space partitions, an index on the space parameter and partition_column
tsdb.associated_schema	REGCLASS	_timescaledb_internal	✖	Set the schema name for internal tables.
tsdb.associated_table_prefix	TEXT	_hyper	✖	Set the prefix for the names of internal s.
tsdb.orderby	TEXT	Descending order on the time column in table_name.	✖	The order in which items are used in the . Specified in the same way as an ORDER BY clause in a SELECT query. Setting tsdb.orderby automatically creates an implicit min/max sparse index on the orderby column.
tsdb.segmentby	TEXT	looks at pg_stats and determines an appropriate column based on the data cardinality and distribution. If pg_stats is not available, looks for an appropriate column from the existing indexes.	✖	Set the list of columns used to segment data in the for table. An identifier representing the source of the data such as device_id or tags_id is usually a good candidate.
tsdb.sparse_index	TEXT	evaluates the columns you already have indexed, checks which data types are a good fit for sparse indexing, then creates a sparse index as an optimization.	✖	Configure the sparse indexes for compressed s. Requires setting tsdb.orderby. Supported index types include: bloom(<column_name>): a probabilistic index, effective for = filters. Cannot be applied to tsdb.orderby columns. minmax(<column_name>): stores min/max values for each compressed . Setting tsdb.orderby automatically creates an implicit min/max sparse index on the orderby column. Define multiple indexes using a comma-separated list. You can set only one index per column. Set to an empty string to avoid using sparse indexes and explicitly disable the default behavior.

Returns

Return Value	Type	Description
CREATE TABLE	Command tag	Command completed successfully

On failure, an error is returned:

Error	Description
partition column could not be determined	No timestamp column found for automatic partitioning. Use tsdb.partition_column to specify the partitioning column.
column "<name>" does not exist	The specified partition column does not exist in the table.
timescaledb options requires hypertable option	options used without setting tsdb.hypertable=true.
invalid input syntax for type <type>	Invalid value for tsdb.chunk_interval for the partition column type.
invalid value for tsdb.create_default_indexes '<value>'	Value for tsdb.create_default_indexes must be a boolean.
unrecognized parameter "<param>"	Invalid parameter specified.
functionality not supported under the current "apache" license	Feature requires a license with additional capabilities.