Constraints are rules that apply to your database columns. This prevents you from entering invalid data into your database. When you create, change, or delete constraints on your hypertables, the constraints are propagated to the underlying chunks, and to any indexes.

Hypertables support all standard PostgreSQL constraint types, except for foreign key constraints on other tables that reference values in a hypertable.

For example, you can create a table that only allows positive device IDs, and non-null temperature readings. You can also check that time values for all devices are unique. To create this table, with the constraints, use this command:

CREATE TABLE conditions (
time TIMESTAMPTZ
temp FLOAT NOT NULL,
device_id INTEGER CHECK (device_id > 0),
location INTEGER REFERENCES locations (id),
PRIMARY KEY(time, device_id)
);
SELECT create_hypertable('conditions', by_range('time'));
Note

The by_range dimension builder is an addition to TimescaleDB 2.13.

This example also references values in another locations table using a foreign key constraint.

Note

Time columns used for partitioning must not allow NULL values. A NOT NULL constraint is added by default to these columns if it doesn't already exist.

For more information on how to manage constraints, see the PostgreSQL docs.

Keywords

Found an issue on this page?

Report an issue!