Timescaledb: Work with Django / tables with auto ID?

[Kooludo]

Hi there.

I'm attempting to create a hypertable with a table generated by Django but when I try to call the create_hypertable function I get an error:

postgres=# SELECT create_hypertable('example', 'time');
ERROR:  Cannot create a unique index without the time column
CONTEXT:  PL/pgSQL function _timescaledb_internal.get_general_index_definition(regclass,regclass,_timescaledb_catalog.hypertable) line 27 at RAISE
SQL statement "SELECT 1
    FROM pg_index,
    LATERAL _timescaledb_meta_api.add_index(
        hypertable_row.id,
        hypertable_row.schema_name,
        (SELECT relname FROM pg_class WHERE oid = indexrelid::regclass),
        _timescaledb_internal.get_general_index_definition(indexrelid, indrelid, hypertable_row)
    )
    WHERE indrelid = main_table"
PL/pgSQL function create_hypertable(regclass,name,name,integer,smallint,name,name,_timescaledb_catalog.chunk_placement_type,bigint,boolean,boolean) line 97 at PERFORM

I think it's something to do with the auto PK that Django tries to create.
It may well be the correct approach for me to separate out my timescaledb database and my Django managed ones, but I thought it was worth posting here first incase there was some obvious way round this or if you could let me know what specifically it doesn't like about the Django table.

If it's of any use I've created a small example Django project and pasted below a pg_dump to show what Django's created tables look like.

CREATE TABLE example (
    id integer NOT NULL,
    "time" timestamp with time zone NOT NULL
);


CREATE SEQUENCE example_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;

ALTER SEQUENCE example_id_seq OWNED BY example.id;
ALTER TABLE ONLY example ALTER COLUMN id SET DEFAULT nextval('example_id_seq'::regclass);
SELECT pg_catalog.setval('example_id_seq', 1, false);
ALTER TABLE ONLY example
    ADD CONSTRAINT example_pkey PRIMARY KEY (id);

Thanks very much for any assistance you can give me on this. Please let me know if I can add anything else to help.

[cevian]

So the problem here is that django is creating a primary key on the id column. Because we partition hypertables by time we cannot support a primary key that doesn't involve the time column (It would be hard/impossible to guarantee uniqueness of the id column across time partitions). You have a few options:

1) Include the time column in the primary key
2) Do not have a primary key

I don't know what would be easier in Django.

[cevian]

As an aside, in time-series data it is rare that you actually want a non-time based primary key. For example, upserts are usually done on data points based on time. In a sense, "time" is the primary key for a lot of time-series data.

[Kooludo]

This is really helpful, thanks.

My data is ticker events which come at irregular times. It's possible that multiple might come at exactly the same time from different sources, so I think the solution for me is a table with no primary key. I'll probably need to step outside Django for this.

Thanks for your very prompt help.

[nyejon]

@Kooludo How did you end up solving this? Did you move away from the Django ORM to SQL Alchemy?

[dedsm]

I have this same issue, solved it by creating a migration with sql code that removes the primary key constraint and added an index to the column that was the primary key, so far django doesn't complain and the hypertable was created perfectly

[AndyMender]

I would personally recommend a vanilla PostgreSQL client like psycopg2. I myself build a slim TimescaleDB client class based on it utilizing some basic features like create_database (with CREATE EXTENSION IF NOT EXISTS timescaledb CASCADE), create_table, etc. and using cursor objects as context managers for easier encapsulation and automatic clean-up. SQLAlchemy is great for designing tables, but later on you'll need tons of slow boilerplate code to perform UPSERTs.

[francbartoli]

@AndyMender where can I have a look at your timescaledb client implementation?