--- title: CREATE TABLE | Tiger Data Docs description: Create a table or a hypertable --- Since [2.20.0](https://github.com/timescale/timescaledb/releases/tag/2.20.0) Create a hypertable partitioned on a single dimension with columnstore enabled, or create a standard PostgreSQL relational table. A hypertable is a specialized PostgreSQL table that automatically partitions your data by time. All actions that work on a PostgreSQL table, work on hypertables. For example, [ALTER TABLE](/docs/reference/timescaledb/hypercore/alter_table/index.md) and [SELECT](https://www.postgresql.org/docs/current/sql-select.html). By default, a hypertable is partitioned on the time dimension. To add secondary dimensions to a hypertable, call [add\_dimension](/docs/reference/timescaledb/hypertables/add_dimension/index.md). To convert an existing relational table into a hypertable, call [create\_hypertable](/docs/reference/timescaledb/hypertables/create_hypertable/index.md). When you create a hypertable using [CREATE TABLE … WITH …](/docs/reference/timescaledb/hypertables/create_table/index.md), the default partitioning column is automatically the first column with a timestamp data type. Also, TimescaleDB creates a [columnstore policy](/docs/reference/timescaledb/hypercore/add_columnstore_policy/index.md) that automatically converts your data to the columnstore, after an interval equal to the value of the [chunk\_interval](/docs/reference/timescaledb/hypertables/set_chunk_time_interval/index.md), defined through `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 columnstore conversion, hypertable chunks are compressed by up to 98%, and organized for efficient, large-scale queries. You can customize this policy later using [alter\_job](/docs/reference/timescaledb/jobs-automation/alter_job/index.md). However, to change `after` or `created_before`, the compression settings, or the hypertable the policy is acting on, you must [remove the columnstore policy](/docs/reference/timescaledb/hypercore/remove_columnstore_policy/index.md) and [add a new one](/docs/reference/timescaledb/hypercore/add_columnstore_policy/index.md). You can also manually [convert chunks](/docs/reference/timescaledb/hypercore/convert_to_columnstore/index.md) in a hypertable to the columnstore. Hypertable to hypertable foreign keys are not allowed, all other combinations are permitted. The columnstore settings are applied on a per-chunk basis. You can change the settings by calling [ALTER TABLE](/docs/reference/timescaledb/hypercore/alter_table/index.md) without first converting the entire hypertable back to the rowstore. The new settings apply only to the chunks that have not yet been converted to columnstore, the existing chunks in the columnstore do not change. Similarly, if you [remove an existing columnstore policy](/docs/reference/timescaledb/hypercore/remove_columnstore_policy/index.md) and then [add a new one](/docs/reference/timescaledb/hypercore/add_columnstore_policy/index.md), the new policy applies only to the unconverted chunks. This means that chunks with different columnstore settings can co-exist in the same hypertable. TimescaleDB calculates default columnstore settings for each chunk when it is created. These settings apply to each chunk, and not the entire hypertable. To explicitly disable the defaults, set a setting to an empty string. `CREATE TABLE` extends the standard PostgreSQL [CREATE TABLE](https://www.postgresql.org/docs/current/sql-createtable.html). This page explains the features and arguments specific to TimescaleDB. Note For TimescaleDB [v2.23.0](https://github.com/timescale/timescaledb/releases/tag/2.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, TimescaleDB throws an error and asks for an explicit definition. For earlier versions, set `partition_column` to a time column. If you are self-hosting TimescaleDB [v2.20.0](https://github.com/timescale/timescaledb/releases/tag/2.23.0) to [v2.22.1](https://github.com/timescale/timescaledb/releases/tag/2.23.0), to convert your data to the columnstore after a specific time interval, you have to call [add\_columnstore\_policy](/docs/reference/timescaledb/hypercore/add_columnstore_policy/index.md) after you call [CREATE TABLE](/docs/reference/timescaledb/hypertables/create_table/index.md) If you are self-hosting TimescaleDB [v2.19.3](https://github.com/timescale/timescaledb/releases/tag/2.19.3) and below, create a [PostgreSQL relational table](https://www.postgresql.org/docs/current/sql-createtable.html), then convert it using [create\_hypertable](/docs/reference/timescaledb/hypertables/create_hypertable/index.md). You then enable hypercore with a call to [ALTER TABLE](/docs/reference/timescaledb/hypercore/alter_table/index.md). ## 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 hypertable using `CREATE TABLE WITH`, TimescaleDB automatically creates a [columnstore policy](/docs/reference/timescaledb/hypercore/add_columnstore_policy/index.md) that uses the chunk 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 `TIMESTAMPTZ` 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 - [PostgreSQL 17 and lower](#tab-panel-670) - [PostgreSQL 18](#tab-panel-671) ``` -- 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'); ``` ``` -- 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 columnstore, the IO footprint is significantly lower. Also, the [columnstore policy](/docs/reference/timescaledb/hypercore/add_columnstore_policy/index.md) you set is less important, `INSERT` already produces compressed chunks. Note 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 columnstore 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 hypertable: ``` CREATE TABLE t(time timestamptz, device text, value float) WITH (tsdb.hypertable); ``` 2. Copy data into the hypertable: 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 PostgreSQL relational table ``` CREATE TABLE IF NOT EXISTS relational_table( device text, value float ); ``` ## Arguments The syntax is: ``` CREATE TABLE ( -- Standard Postgres syntax for CREATE TABLE ) WITH ( tsdb.hypertable = true | false, tsdb.columnstore = true | false, tsdb.partition_column = '', tsdb.chunk_interval = '', tsdb.create_default_indexes = true | false, tsdb.associated_schema = '', tsdb.associated_table_prefix = '', tsdb.orderby = ' [ASC | DESC] [ NULLS { FIRST | LAST } ] [, ...]', tsdb.segmentby = ' [, ...]', tsdb.sparse_index = '(), index()' ) ``` | Name | Type | Default | Required | Description | | ------------------------------ | ------- | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | -------- | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | `tsdb.hypertable` | BOOLEAN | `true` | ✖ | Create a new hypertable for time-series data rather than a standard PostgreSQL relational table. | | `tsdb.columnstore` | BOOLEAN | `true` | ✖ | Enable or disable columnstore on the hypertable. When enabled, TimescaleDB automatically creates a [columnstore policy](/docs/reference/timescaledb/hypercore/add_columnstore_policy/index.md) with `after` set to the chunk interval and a schedule interval of 1 day. | | `tsdb.partition_column` | TEXT | The first `TIMESTAMP` or `TIMESTAMPTZ` column in the table | ✖ | 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 chunk stores data from the same day. Data from different days is stored in different chunks. | | `tsdb.create_default_indexes` | BOOLEAN | `true` | ✖ | Set to `false` to not automatically create indexes. The default indexes are:* On all hypertables, a descending index on `partition_column` * On hypertables with space partitions, an index on the space parameter and `partition_column` | | `tsdb.associated_schema` | TEXT | `_timescaledb_internal` | ✖ | Set the schema name for internal hypertable tables. | | `tsdb.associated_table_prefix` | TEXT | `_hyper` | ✖ | Set the prefix for the names of internal hypertable chunks. | | `tsdb.orderby` | TEXT | Descending order on the time column in `table_name`. | ✖ | The order in which items are used in the columnstore. 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 | TimescaleDB looks at [`pg_stats`](https://www.postgresql.org/docs/current/view-pg-stats.html) and determines an appropriate column based on the data cardinality and distribution. If `pg_stats` is not available, TimescaleDB looks for an appropriate column from the existing indexes. | ✖ | Set the list of columns used to segment data in the columnstore 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 | TimescaleDB 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 chunks. Requires setting `tsdb.orderby`. Supported index types include:- `bloom()`: a probabilistic index, effective for `=` filters. Cannot be applied to `tsdb.orderby` or `tsdb.segmentby` columns.- `minmax()`: stores min/max values for each compressed chunk. Cannot be applied to `tsdb.orderby` or `tsdb.segmentby` columns. 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 "" does not exist` | The specified partition column does not exist in the table. | | `timescaledb options requires hypertable option` | TimescaleDB options used without setting `tsdb.hypertable=true`. | | `invalid input syntax for type ` | Invalid value for `tsdb.chunk_interval` for the partition column type. | | `invalid value for tsdb.create_default_indexes ''` | Value for `tsdb.create_default_indexes` must be a boolean. | | `unrecognized parameter ""` | Invalid TimescaleDB parameter specified. | | `functionality not supported under the current "apache" license` | Feature requires a TimescaleDB license with additional capabilities. |