create_table.mdx

title	CREATE TABLE
description	Create a table or a hypertable
products	cloud self_hosted
keywords	hypertables create

import SinceRelease from '@components/SinceRelease.astro';

import { Tabs, Tab } from '@stainless-api/docs/stainless-docs/mintlify-compat';

import { Callout } from '@stainless-api/docs/components';

import OldCreateHypertable from '@partials/_old-api-create-hypertable.mdx'; import CreateHypertableColumnstorePolicyNote from '@partials/_create-hypertable-columnstore-policy-note.mdx'; import DimensionInfo from '@partials/_dimensions_info.mdx'; import HypercoreDirectCompress from '@partials/_hypercore-direct-compress.mdx';

import * as C from '@constants';

Create a {C.HYPERTABLE} partitioned on a single dimension with {C.COLUMNSTORE} enabled, or create a standard {C.PG} relational table.

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

{C.HYPERTABLE_CAP} to {C.HYPERTABLE} foreign keys are not allowed, all other combinations are permitted.

The {C.COLUMNSTORE} settings are applied on a per-{C.CHUNK} basis. You can change the settings by calling ALTER TABLE without first converting the entire {C.HYPERTABLE} back to the {C.ROWSTORE}. The new settings apply only to the {C.CHUNK}s that have not yet been converted to {C.COLUMNSTORE}, the existing {C.CHUNK}s in the {C.COLUMNSTORE} 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 {C.CHUNK}s. This means that {C.CHUNK}s with different {C.COLUMNSTORE} settings can co-exist in the same {C.HYPERTABLE}.

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

CREATE TABLE extends the standard {C.PG} CREATE TABLE. This page explains the features and arguments specific to {C.TIMESCALE_DB}.

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 {C.HYPERTABLE} using CREATE TABLE WITH, {C.TIMESCALE_DB} automatically creates a columnstore policy that uses the {C.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

```sql -- 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'); ```

<Tab title={${C.PG} 18}> sql -- 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

1. Create a {C.HYPERTABLE}:

   CREATE TABLE t(time timestamptz, device text, value float) WITH (tsdb.hypertable);

2. Copy data into the {C.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 {C.PG} 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.columnstore = 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>)',
   tsdb.direct_compress = true | false
)

Name	Type	Default	Required	Description
tsdb.hypertable	BOOLEAN	true	✖	Create a new {C.HYPERTABLE} for time-series data rather than a standard {C.PG} relational table.
tsdb.columnstore	BOOLEAN	true	✖	Enable or disable {C.COLUMNSTORE} on the {C.HYPERTABLE}. When enabled, {C.TIMESCALE_DB} automatically creates a columnstore policy with after set to the {C.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 {C.CHUNK} stores data from the same day. Data from different days is stored in different {C.CHUNK}s.
tsdb.create_default_indexes	BOOLEAN	true	✖	Set to false to not automatically create indexes. The default indexes are: On all {C.HYPERTABLE}s, a descending index on partition_column On {C.HYPERTABLE}s with space partitions, an index on the space parameter and partition_column
tsdb.associated_schema	TEXT	_timescaledb_internal	✖	Set the schema name for internal {C.HYPERTABLE} tables.
tsdb.associated_table_prefix	TEXT	_hyper	✖	Set the prefix for the names of internal {C.HYPERTABLE} {C.CHUNK}s.
tsdb.orderby	TEXT	Descending order on the time column in table_name.	✖	The order in which items are used in the {C.COLUMNSTORE}. Specified in the same way as an ORDER BY clause in a SELECT query. Setting tsdb.orderby automatically creates an implicit sparse index on the orderby column: a firstlast index since 2.28.0, minmax before.
tsdb.segmentby	TEXT	{C.TIMESCALE_DB} looks at pg_stats and determines an appropriate column based on the data cardinality and distribution. If pg_stats is not available, {C.TIMESCALE_DB} looks for an appropriate column from the existing indexes.	✖	Set the list of columns used to segment data in the {C.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	{C.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 {C.CHUNK}s. Requires setting tsdb.orderby. Supported index types are bloom(<column>, …) for equality filters and minmax(<column>) for range filters; use a comma-separated list to set multiple. For when each is best, configuration patterns, and restrictions, see Sparse indexes on the columnstore. Set to an empty string to disable sparse indexes.
tsdb.direct_compress	BOOLEAN	false	✖	Compress data in memory during ingestion and write compressed batches directly to the {C.COLUMNSTORE}, instead of writing uncompressed rows first. Only takes effect when the {C.COLUMNSTORE} is enabled. The {C.COLUMNSTORE} is on by default, so no extra setting is required. If you set tsdb.columnstore = false in the same statement, direct_compress is silently ignored. See Improve performance with direct compress.

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	{C.TIMESCALE_DB} 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 {C.TIMESCALE_DB} parameter specified.
functionality not supported under the current "apache" license	Feature requires a {C.TIMESCALE_DB} license with additional capabilities. To check your edition, see Compare {C.TIMESCALE_DB} editions.