Creating a hypertable with timescaleDB and django fails. Cannot remove PK restriction

Question

I am running a django project and I am trying to integrate timescaleDB. Unfortunately this is not plug and play since django does not support timescaleDB officially. What needs to be done is some manual migrations. I tried two ways, but both ways are not working for me. Both are not working because my manually written SQL is not working:

1. Adjusting migration manually (Problem: Unique restraint is not removed)

I am following this tutorial (https://blog.ashtonhudson.com/adding-timescale-to-django.html) which is exactly my use case.

I first create my models with timestamp as pk

from django.db import models
from django.db.models.fields import DateTimeField

class Metric(models.Model):
    drinks = models.CharField(max_length=200)

class TimeData(models.Model):
    # NOTE: We have removed the primary key (unique constraint) manually, since
    # we don't want an id column.
    timestamp = DateTimeField(primary_key=True)
    metric = models.ForeignKey(Parameter, on_delete=models.RESTRICT)
    value = models.FloatField(null=False)

I then run the migrations and manually add two SQL statements to remove the unique constraint from the timestamp primary key:

class Migration(migrations.Migration):

    operations = [
        ...
        migrations.CreateModel(
            name="TimeData",
            fields=[
                ("timestamp", models.DateTimeField(primary_key=True, serialize=False)),
                ("value", models.FloatField()),
                (
                    "metric",
                    models.ForeignKey(
                        on_delete=django.db.models.deletion.RESTRICT,
                        to="myapp.drinks",
                    ),
                ),
            ],
        ),
        migrations.RunSQL(
            "ALTER TABLE myapp_timedata DROP CONSTRAINT myapp_timedata_pkey;"
        ),
        migrations.RunSQL(
            "SELECT create_hypertable('myapp_timedata', 'timestamp', chunk_time_interval => INTERVAL '5 days');"
        ),
    ]

This creates a hypertable, but there is still the PK-constraint on the timestamp column.

2. The second thing I try is to manually write a SQL migration to create a table with a composite primary key (not supported by django) and then set the model to unmanaged (Idea from django con https://www.youtube.com/watch?v=hXctK2NQTsc). The problem is the same

I first write this migration manually:

# Generated manually

from django.db import migrations


class Migration(migrations.Migration):
    dependencies = [
        ...
    ]

    operations = [
        migrations.RunSQL(
            "CREATE TABLE public.myapp_timeseriesdata ( \
                    value float NOT NULL, \
                    timestamp timestamp NOT NULL, \
                    timeseriesunit_meta_id int4 NOT NULL, \
                    CONSTRAINT myapp_timeseriesdata_pkey PRIMARY KEY (timestamp, timeseriesdata_metric_id) \
                );"),
        migrations.RunSQL(
            "SELECT create_hypertable('myapp_timeseriesdata', 'timestamp', chunk_time_interval => INTERVAL '7 days');"
        ),
    ]

Afterwards I add the model:

class TimeSeriesData(models.Model):
    timestamp = models.DateTimeField(primary_key=True)
    value = models.FloatField()
    metric = models.ForeignKey(
        Metric, on_delete=models.CASCADE
    )

    class Meta:
        managed = False
        db_table = "myapp_timeseriesdata"
        unique_together = (("metric", "timestamp"),)

This creates a hypertable but also has the constraint that the timestamps have to be unique

Anyone has an idea what is happening or why the constraint does not get removed? Thanks very much in advance for all the help.

Answer 1

It seems this line timestamp = models.DateTimeField(primary_key=True) is saying this is the primary key.

You need to exchange it saying it’s not the primary key and build a composite primary key.