> ## Documentation Index > Fetch the complete documentation index at: https://mintlify-poc.mintlify.site/llms.txt > Use this file to discover all available pages before exploring further. # CREATE TABLE > Create a table or a hypertable export const CLOUD_LONG = 'Tiger Cloud'; export const ROWSTORE = 'rowstore'; export const HYPERTABLE_CAP = 'Hypertable'; export const CHUNK = 'chunk'; export const HYPERTABLE = 'hypertable'; export const HYPERCORE = 'hypercore'; export const PG = 'Postgres'; export const COLUMNSTORE = 'columnstore'; export const TIMESCALE_DB = 'TimescaleDB'; Since [2.20.0][tsdb-2.20.0] Create a {HYPERTABLE} partitioned on a single dimension with {COLUMNSTORE} enabled, or create a standard {PG} relational table. A {HYPERTABLE} is a specialized {PG} table that automatically partitions your data by time. All actions that work on a {PG} table, work on {HYPERTABLE}s. For example, [ALTER TABLE][alter_table_hypercore] and [SELECT][sql-select]. By default, a {HYPERTABLE} is partitioned on the time dimension. To add secondary dimensions to a {HYPERTABLE}, call [add\_dimension][add-dimension]. To convert an existing relational table into a {HYPERTABLE}, call [create\_hypertable][create_hypertable]. When you create a {HYPERTABLE} using [CREATE TABLE ... WITH ...][hypertable-create-table], the default partitioning column is automatically the first column with a timestamp data type. Also, {TIMESCALE_DB} creates a [columnstore policy][add_columnstore_policy] that automatically converts your data to the {COLUMNSTORE}, after an interval equal to the value of the [chunk\_interval][create_table_arguments], 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 {COLUMNSTORE} conversion, {HYPERTABLE} {CHUNK}s are compressed by up to 98%, and organized for efficient, large-scale queries. You can customize this policy later using [alter\_job][alter_job_samples]. However, to change `after` or `created_before`, the compression settings, or the {HYPERTABLE} the policy is acting on, you must [remove the columnstore policy][remove_columnstore_policy] and [add a new one][add_columnstore_policy]. You can also manually [convert {CHUNK}s][convert_to_columnstore] in a {HYPERTABLE} to the {COLUMNSTORE}. [add_columnstore_policy]: /api-reference/timescaledb/hypercore/add_columnstore_policy [alter_job_samples]: /api-reference/timescaledb/jobs-automation/alter_job#samples [convert_to_columnstore]: /api-reference/timescaledb/hypercore/convert_to_columnstore [create_table_arguments]: /api-reference/timescaledb/hypertables/create_table#arguments [hypertable-create-table]: /api-reference/timescaledb/hypertables/create_table [remove_columnstore_policy]: /api-reference/timescaledb/hypercore/remove_columnstore_policy {HYPERTABLE_CAP} 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][alter_table_hypercore] without first converting the entire {HYPERTABLE} back to the {ROWSTORE}. The new settings apply only to the {CHUNK}s that have not yet been converted to {COLUMNSTORE}, the existing {CHUNK}s in the {COLUMNSTORE} do not change. Similarly, if you [remove an existing columnstore policy][remove_columnstore_policy] and then [add a new one][add_columnstore_policy], the new policy applies only to the unconverted {CHUNK}s. This means that {CHUNK}s with different {COLUMNSTORE} settings can co-exist in the same {HYPERTABLE}. {TIMESCALE_DB} 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 {PG} [CREATE TABLE][pg-create-table]. This page explains the features and arguments specific to {TIMESCALE_DB}. For {TIMESCALE_DB} [v2.23.0][tsdb-release-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, {TIMESCALE_DB} throws an error and asks for an explicit definition. For earlier versions, set `partition_column` to a time column. If you are self-hosting {TIMESCALE_DB} [v2.20.0][tsdb-release-2-23-0] to [v2.22.1][tsdb-release-2-23-0], to convert your data to the {COLUMNSTORE} after a specific time interval, you have to call [add\_columnstore\_policy][add_columnstore_policy] after you call [CREATE TABLE][hypertable-create-table] If you are self-hosting {TIMESCALE_DB} [v2.19.3][tsdb-release-2-19-3] and below, create a [{PG} relational table][pg-create-table], then convert it using [create\_hypertable][create_hypertable]. You then enable {HYPERCORE} with a call to [ALTER TABLE][alter_table_hypercore]. [pg-create-table]: https://www.postgresql.org/docs/current/sql-createtable.html [create_hypertable]: /api-reference/timescaledb/hypertables/create_hypertable [alter_table_hypercore]: /api-reference/timescaledb/hypercore/alter_table [add_columnstore_policy]: /api-reference/timescaledb/hypercore/add_columnstore_policy [hypertable-create-table]: /api-reference/timescaledb/hypertables/create_table [chunk_interval]: /api-reference/timescaledb/hypertables/set_chunk_time_interval [tsdb-release-2-23-0]: https://github.com/timescale/timescaledb/releases/tag/2.23.0 [tsdb-release-2-20-0]: https://github.com/timescale/timescaledb/releases/tag/2.20.0 [tsdb-release-2-22-1]: https://github.com/timescale/timescaledb/releases/tag/2.22.1 [tsdb-release-2-19-3]: https://github.com/timescale/timescaledb/releases/tag/2.19.3 ## Samples ### Create a hypertable partitioned on the time dimension and enable columnstore ```sql theme={"dark"} 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`, {TIMESCALE_DB} automatically creates a [columnstore policy][add_columnstore_policy] 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 timestampz data type. ### Create a hypertable partitioned on the time with fewer chunks based on time interval ```sql theme={"dark"} 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 ```sql theme={"dark"} -- 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'); ``` ```sql theme={"dark"} -- 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][add_columnstore_policy] you set is less important, `INSERT` already produces compressed {CHUNK}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: ```sql theme={"dark"} 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. [add_columnstore_policy]: /api-reference/timescaledb/hypercore/add_columnstore_policy 1. Create a {HYPERTABLE}: ```sql theme={"dark"} 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. ```sql theme={"dark"} COPY t FROM '/tmp/t.binary' WITH (format binary); ``` ### Create a Postgres relational table ```sql theme={"dark"} CREATE TABLE IF NOT EXISTS relational_table( device text, value float ); ``` ## Arguments The syntax is: ```sql theme={"dark"} CREATE TABLE ( -- Standard Postgres syntax for CREATE TABLE ) WITH ( tsdb.hypertable = 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 {PG} 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 {CHUNK} stores data from the same day. Data from different days is stored in different {CHUNK}s. | | `tsdb.create_default_indexes` | BOOLEAN | `true` | ✖ | Set to `false` to not automatically create indexes.
The default indexes are:
  • On all {HYPERTABLE}s, a descending index on `partition_column`
  • On {HYPERTABLE}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 {HYPERTABLE} tables. | | `tsdb.associated_table_prefix` | TEXT | `_hyper` | ✖ | Set the prefix for the names of internal {HYPERTABLE} {CHUNK}s. | | `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 | {TIMESCALE_DB} looks at [`pg_stats`][pgstats] and determines an appropriate column based on the data cardinality and distribution. If `pg_stats` is not available, {TIMESCALE_DB} 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 | {TIMESCALE_DB} 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 {CHUNK}s. Requires setting `tsdb.orderby`. Supported index types include:
  • `bloom()`: a probabilistic index, effective for `=` filters. Cannot be applied to `tsdb.orderby` columns.
  • `minmax()`: stores min/max values for each compressed {CHUNK}. 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` | {TIMESCALE_DB} 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 {TIMESCALE_DB} parameter specified. | | `functionality not supported under the current "apache" license` | Feature requires a {TIMESCALE_DB} license with additional capabilities. | [add-dimension]: /api-reference/timescaledb/hypertables/add_dimension [add_columnstore_policy]: /api-reference/timescaledb/hypercore/add_columnstore_policy [alter_table_hypercore]: /api-reference/timescaledb/hypercore/alter_table [create_hypertable]: /api-reference/timescaledb/hypertables/create_hypertable [pg-create-table]: https://www.postgresql.org/docs/current/sql-createtable.html [pgstats]: https://www.postgresql.org/docs/current/view-pg-stats.html [remove_columnstore_policy]: /api-reference/timescaledb/hypercore/remove_columnstore_policy [sql-select]: https://www.postgresql.org/docs/current/sql-select.html [tsdb-2.20.0]: https://github.com/timescale/timescaledb/releases/tag/2.20.0 [uuidv7_functions]: /api-reference/timescaledb/uuid-functions