Getting psycopg2.DatabaseError: cannot create a unique index without the column "time" (used in partitioning)

[Niqnil]

I'm a newbie trying to use timescaledb with my django project. I'm having trouble running the initial migration despite trying the two implementation methods listed in point 3 of your quickstart. From the traceback, it looks like a problem with userauth.models.CustomUser, which inherits from core.models.CreationModificationDateBase, which inherits TimescaleDateTimeField/TimescaleModel. I can't figure out what I'm missing so I'm hoping to get some help.

# traceback
Operations to perform:
  Apply all migrations: account, admin, auth, contenttypes, sessions, sites, socialaccount, userauth
Running migrations:
  Applying userauth.0001_initial...Traceback (most recent call last):
  File "/usr/local/lib/python3.9/site-packages/django/db/backends/utils.py", line 84, in _execute
    return self.cursor.execute(sql, params)
psycopg2.DatabaseError: cannot create a unique index without the column "time" (used in partitioning)

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/code/manage.py", line 22, in <module>
    main()
  File "/code/manage.py", line 18, in main
    execute_from_command_line(sys.argv)
  File "/usr/local/lib/python3.9/site-packages/django/core/management/__init__.py", line 419, in execute_from_command_line
    utility.execute()
  File "/usr/local/lib/python3.9/site-packages/django/core/management/__init__.py", line 413, in execute
    self.fetch_command(subcommand).run_from_argv(self.argv)
  File "/usr/local/lib/python3.9/site-packages/django/core/management/base.py", line 354, in run_from_argv
    self.execute(*args, **cmd_options)
  File "/usr/local/lib/python3.9/site-packages/django/core/management/base.py", line 398, in execute
    output = self.handle(*args, **options)
  File "/usr/local/lib/python3.9/site-packages/django/core/management/base.py", line 89, in wrapped
    res = handle_func(*args, **kwargs)
  File "/usr/local/lib/python3.9/site-packages/django/core/management/commands/migrate.py", line 244, in handle
    post_migrate_state = executor.migrate(
  File "/usr/local/lib/python3.9/site-packages/django/db/migrations/executor.py", line 117, in migrate
    state = self._migrate_all_forwards(state, plan, full_plan, fake=fake, fake_initial=fake_initial)
  File "/usr/local/lib/python3.9/site-packages/django/db/migrations/executor.py", line 147, in _migrate_all_forwards
    state = self.apply_migration(state, migration, fake=fake, fake_initial=fake_initial)
  File "/usr/local/lib/python3.9/site-packages/django/db/migrations/executor.py", line 227, in apply_migration
    state = migration.apply(state, schema_editor)
  File "/usr/local/lib/python3.9/site-packages/django/db/migrations/migration.py", line 126, in apply
    operation.database_forwards(self.app_label, schema_editor, old_state, project_state)
  File "/usr/local/lib/python3.9/site-packages/django/db/migrations/operations/models.py", line 92, in database_forwards
    schema_editor.create_model(model)
  File "/usr/local/lib/python3.9/site-packages/timescale/db/backends/postgresql/schema.py", line 120, in create_model
    self._create_hypertable(model, field)
  File "/usr/local/lib/python3.9/site-packages/timescale/db/backends/postgresql/schema.py", line 98, in _create_hypertable
    self.execute(sql)
  File "/usr/local/lib/python3.9/site-packages/django/db/backends/base/schema.py", line 145, in execute
    cursor.execute(sql, params)
  File "/usr/local/lib/python3.9/site-packages/django/db/backends/utils.py", line 98, in execute
    return super().execute(sql, params)
  File "/usr/local/lib/python3.9/site-packages/django/db/backends/utils.py", line 66, in execute
    return self._execute_with_wrappers(sql, params, many=False, executor=self._execute)
  File "/usr/local/lib/python3.9/site-packages/django/db/backends/utils.py", line 75, in _execute_with_wrappers
    return executor(sql, params, many, context)
  File "/usr/local/lib/python3.9/site-packages/django/db/backends/utils.py", line 84, in _execute
    return self.cursor.execute(sql, params)
  File "/usr/local/lib/python3.9/site-packages/django/db/utils.py", line 90, in __exit__
    raise dj_exc_value.with_traceback(traceback) from exc_value
  File "/usr/local/lib/python3.9/site-packages/django/db/backends/utils.py", line 84, in _execute
    return self.cursor.execute(sql, params)
django.db.utils.DatabaseError: cannot create a unique index without the column "time" (used in partitioning)

# core.models.py version 1

from django.db import models
from django.utils.translation import gettext_lazy as _
from timescale.db.models.fields import TimescaleDateTimeField
from timescale.db.models.managers import TimescaleManager
from timescale.db.models.models import TimescaleModel

class CreationModificationDateBase(models.Model):
    """Abstract base class with a creation and modification date and time."""
    time = TimescaleDateTimeField(_("Creation Date and Time"), auto_now_add=True, interval="1 day")
    modified = models.DateTimeField(_("Modification Date and Time"), auto_now=True,)

    objects = models.Manager()
    timescale = TimescaleManager()

    class Meta:
        abstract = True

# core.models.py version 2

class CreationModificationDateBase(TimescaleModel):
    """Abstract base class with a creation and modification date and time."""
    modified = models.DateTimeField(_("Modification Date and Time"), auto_now=True,)

    class Meta:
        abstract = True

# userauth.models.py

from django.db import models
from django.contrib.auth.models import AbstractUser
from django.core.validators import RegexValidator
from django.utils.translation import gettext_lazy as _
from django_countries.fields import CountryField
import sys
sys.path.append("..") # Adds higher directory to python modules path.
from core.models import CreationModificationDateBase

class CustomUser(AbstractUser, CreationModificationDateBase):
    display_name = models.CharField(verbose_name=_("Display name"), max_length=30, help_text=_("Will be shown e.g. when commenting"))
    date_of_birth = models.DateField(verbose_name=_("Date of birth"), blank=True, null=True)
    address1 = models.CharField(verbose_name=_("Address line 1"), max_length=1024, blank=True, null=True)
    address2 = models.CharField(verbose_name=_("Address line 2"), max_length=1024, blank=True, null=True)
    zip_code = models.CharField(verbose_name=_("Postal Code"), max_length=12, blank=True, null=True)
    city = models.CharField(verbose_name=_("City"), max_length=1024, blank=True, null=True)
    country = CountryField(blank=True, null=True)
    phone_regex = RegexValidator(regex=r"^\+(?:[0-9]●?){6,14}[0-9]$", message=_("Enter a valid international mobile phone number starting with +(country code)"))
    mobile_phone = models.CharField(validators=[phone_regex], verbose_name=_("Mobile phone"), max_length=17, blank=True, null=True)
    additional_information = models.CharField(verbose_name=_("Additional information"), max_length=4096, blank=True, null=True)
    photo = models.ImageField(verbose_name=_("Photo"), upload_to='photos/', default='photos/default-user-avatar.png')

    class Meta:
        ordering = ['last_name']

    def __str__(self):
        return f"{self.username}: {self.first_name} {self.last_name}"

#Dockerfile

FROM python:3.9.6-slim-buster

# Set environment variables
ENV PYTHONDONTWRITEBYTECODE 1
ENV PYTHONUNBUFFERED 1

# Set work directory
WORKDIR /code

# Install system packages required
RUN apt-get update --yes --quiet && apt-get install --yes --quiet --no-install-recommends \
    build-essential \
    libpq-dev \
    libmariadbclient-dev \
    libjpeg62-turbo-dev \
    zlib1g-dev \
    libwebp-dev \
 && rm -rf /var/lib/apt/lists/*

# Install dependencies
COPY Pipfile Pipfile.lock /code/
RUN pip install pipenv && pipenv install --system --dev

# Copy project
COPY . /code/

# docker-compose.yml
version: '3.8'

services:
    web:
        build: .
        command: python manage.py runserver 0.0.0.0:8000 --settings=IT.settings.dev
        container_name: web
        restart: unless-stopped
        volumes:
            - .:/code
        ports:
            - 8000:8000
        env_file:
            - .env/.dev
        depends_on:
            - timescaledb
            - redis

    timescaledb:
        image: timescale/timescaledb:2.4.2-pg13
        container_name: timescaledb
        restart: unless-stopped
        ports:
            - 5432:5432
        env_file:
            - .env/.dev_db
        volumes:
            - timescaledbdata:/var/lib/postgresql/data
    redis:
        image: redis:6.2.5-alpine3.14
        container_name: redis
        restart: unless-stopped

volumes:
    timescaledbdata:

# Pipfile
[[source]]
url = "https://pypi.org/simple"
verify_ssl = true
name = "pypi"

[packages]
django = "~=3.2.7"
django-timescaledb = "~=0.2.10"
psycopg2-binary = "~=2.9.1"
django-allauth = "~=0.45.0"
django-countries = "*"
celery = "~=5.1.2"
redis = "~=3.5.3"
flower = "~=1.0.0"
pandas = "~=1.3.3"
pillow = "~=8.3.2"

[dev-packages]

[requires]
python_version = "3.9"

[schlunsen]

Your way of using abstract seems a bit wierd to be. Do you actually create another model that inherits from the abstracted model? Anyway, not related to this lib so closing for now. Good luck

[ashishnitinpatil]

I was getting a similar error when trying to use timescaledb for an existing model. I ended up disabling index on my foreign key (not using it as column / space partition, so db_index=False) and explicitly defining indexes in the model's meta. Rearranging the output from makemigrations might also have helped.

[DeeeeLAN]

In my case, I tried creating a unique constraint on a field without including the time field:

models.UniqueConstraint(fields=('token',), name='unique_token_ranks_current')

and I got this error. Adding the time field made the error go away, although I don't think it will behave how I want:

models.UniqueConstraint(fields=('token', 'time'), name='unique_token_time_ranks_current')

This would allow the same token to appear twice with different timestamps, but I only want one of each token. I tried adding both constraints but still got the error.