---
title: Primary keys, time columns, and uniqueness for hypertables | Tiger Data Docs
description: Choose a time partition column, primary keys, and unique constraints so your hypertable matches TimescaleDB rules and your query patterns
---

Every hypertable has a **partitioning column**, usually **time**. That choice drives chunk boundaries, retention, columnstore, and which **unique** and **primary key** constraints are valid. Decide these when you model the table, not after data is loaded.

## Time column

The partition column defines how rows are grouped into **chunks**. It is almost always:

- A **`timestamptz`** (or `timestamp` / `date`) column for event time
- An **integer** (`smallint`, `int`, or `bigint`) column for Unix epoch seconds, milliseconds, or other monotonic keys

### `timestamptz` vs integer time

- **`timestamptz`**: Natural for real-world clocks, time zones, and human-readable queries. Prefer this for most applications.
- **Integer epoch**: Useful when upstream systems already emit epoch integers. If you use background jobs like retention or columnstore policies, set [`integer_now_func`](/docs/reference/timescaledb/hypertables/set_integer_now_func/index.md) so those jobs know what “current” means.

Pick a type you can keep **stable** — changing the partition column later requires a migration.

## Primary keys and unique constraints

TimescaleDB requires that **every unique constraint or primary key includes the partitioning column**. Additionally, if your hypertable uses multiple partitioning dimensions (for example, space partitioning on `device_id`), all partitioning columns must appear in the constraint.

This rule exists because uniqueness is enforced **per chunk**. Including all partitioning columns ensures that PostgreSQL can check the constraint within a single chunk without scanning the entire hypertable.

### Valid examples

A sensor table where each device writes at most one row per timestamp:

```
CREATE TABLE sensor_data (
  time        TIMESTAMPTZ NOT NULL,
  device_id   INT         NOT NULL,
  temperature DOUBLE PRECISION,
  humidity    DOUBLE PRECISION,
  PRIMARY KEY (device_id, time)
) WITH (timescaledb.hypertable);
```

An order events table with a surrogate ID that still includes the partition column:

```
CREATE TABLE order_events (
  time      TIMESTAMPTZ NOT NULL,
  order_id  BIGINT      NOT NULL,
  status    TEXT,
  UNIQUE (order_id, time)
) WITH (timescaledb.hypertable);
```

### Invalid example

A surrogate key that does not include the partition column fails:

```
-- This fails because 'id' alone cannot guarantee uniqueness per chunk
CREATE TABLE bad_example (
  id   SERIAL PRIMARY KEY,
  time TIMESTAMPTZ NOT NULL,
  value DOUBLE PRECISION
) WITH (timescaledb.hypertable);
```

### Unique constraints and columnstore

When you insert into a columnstore chunk that has a unique constraint or primary key, TimescaleDB decompresses data in memory to check for constraint violations. Hypertables **without** unique constraints skip this check, so inserts into columnstore chunks are faster. If your workload is append-only with no duplicates, consider whether you truly need a unique constraint.

### Indexes

Unique constraints create indexes. Those indexes affect **ingest cost** and **storage**. See [Hypertable indexes](/docs/learn/hypertables/hypertable-indexes/index.md) and [Hypertables and unique indexes](/docs/build/performance-optimization/hypertables-and-unique-indexes/index.md) for tuning and edge cases.

## Modeling checklist

- **Partition column**: One clear time or integer column used consistently in queries and policies.
- **Uniqueness**: Express real-world identity (`sensor + time`, `order_id + time`) in keys that **include** all partitioning columns.
- **Nulls**: TimescaleDB enforces `NOT NULL` on the partition column automatically. Make sure your ingest pipeline never sends null values for this column.
- **Future timestamps**: Far-future `time` values create unexpected chunks and affect retention behavior. Validate timestamps at ingest.

## Learn more

- [Wide, narrow, and medium tables](/docs/learn/data-model/wide-narrow-medium-tables/index.md): Choose a table layout for your time-series data.
- [Create and configure a hypertable](/docs/learn/hypertables/creating-and-configuring-hypertables/index.md): `CREATE TABLE` options and configuration.
- [Partition a hypertable](/docs/learn/hypertables/partitioning-hypertables/index.md): Time, integer, and space partitioning.
- [Enforce constraints with unique indexes](/docs/build/performance-optimization/hypertables-and-unique-indexes/index.md): Unique index rules and limitations.
- [CREATE TABLE reference](/docs/reference/timescaledb/hypertables/create_table/index.md): Full API reference.