ERROR: cannot create a unique index without the column "date_time" (used in partitioning)

Question

I just started using timescaleDB with postgresql. I have a database named storage_db which contains a table named day_ahead_prices.

After installing timescaledb, I was following Migrate from the same postgresql database to migrate my storage_db into a timescaledb.

When I did (indexes included):

CREATE TABLE tsdb_day_ahead_prices (LIKE day_ahead_prices INCLUDING DEFAULTS INCLUDING CONSTRAINTS INCLUDING INDEXES);
select create_hypertable('tsdb_day_ahead_prices', 'date_time');

It gave me the following error:

ERROR:  cannot create a unique index without the column "date_time" (used in partitioning)

But when I did (indexed excluded):

CREATE TABLE tsdb_day_ahead_prices (LIKE day_ahead_prices INCLUDING DEFAULTS INCLUDING CONSTRAINTS EXCLUDING INDEXES);
select create_hypertable('tsdb_day_ahead_prices', 'date_time');

It was successful. Following which, I did

select create_hypertable('tsdb_day_ahead_prices', 'date_time');

and it gave me the following output:

         create_hypertable          
------------------------------------
 (3,public,tsdb_day_ahead_prices,t)
(1 row)

I am a bit new to this so can anyone please explain to me what is the difference between both of them and why was I getting an error in the first case?

P.S.:

My day_ahead_prices looks as follows:

 id | country_code | values  |         date_time          
----+--------------+---------+----------------------------
  1 | LU           | 100.503 | 2020-04-11 14:04:30.461605
  2 | LU           | 100.503 | 2020-04-11 14:18:39.600574
  3 | DE           |  106.68 | 2020-04-11 15:59:10.223965

Edit 1:

I created the day_ahead_prices table in python using flask and flask_sqlalchemy and the code is:

class day_ahead_prices(db.Model):
    __tablename__ = "day_ahead_prices"
    id = db.Column(db.Integer, primary_key=True)
    country_code = db.Column(avail_cc_enum, nullable=False)
    values = db.Column(db.Float(precision=2), nullable=False)
    date_time = db.Column(db.DateTime, default=datetime.now(tz=tz), nullable=False)

    def __init__(self, country_code, values):
        self.country_code = country_code
        self.values = values

Answer 1

When executing CREATE TABLE tsdb_day_ahead_prices (LIKE day_ahead_prices INCLUDING DEFAULTS INCLUDING CONSTRAINTS INCLUDING INDEXES); you're telling the database to create the tsdb_day_ahead_prices table using the day_ahead_prices as a template (same columns, same types for those columns), but you're also telling it to include the default values, constraints and indexes that you have defined on the original table, and apply/create the same for your new table.

Then you are executing the timescaledb command that makes the tsdb_day_ahead_prices table a hypertable. A hypertable is an abstraction that hides away the partitioning of the physical table. (https://www.timescale.com/products/how-it-works). You are telling TimescaleDB to make the tsdb_day_ahead_prices a hypertable using the date_time column as a partitioning key.

When creating hypertables, one constraing that TimescaleDB imposes is that the partitioning column (in your case 'date_time') must be included in any unique indexes (and Primary Keys) for that table. (https://docs.timescale.com/latest/using-timescaledb/schema-management#indexing-best-practices)

The first error you get cannot create a unique index without the column "date_time" is exactly because of this. You copied the primary key definition on the id column. So the primary key is preventing the table to be a hypertable.

The second time, you created the tsdb_day_ahead_prices table but you didn't copy the indexes from the original table, so the primary key is not defined (which is really a unique index). So the creation of the hypertable was successfull.

The output you get from the create_hypertable function tells you that you have a new hypertable, in the public schema, the name of the hypertable, and the internal id that timescaledb uses for it.

So now you can use the tsdb_day_ahead_prices as normal, and timescaledb underneath will make sure the data goes into the proper partitions/chunks

Does the id need to be unique for this table? If you're going to be keeping time-series data then each row may not really be unique for each id, but may be uniquely identified by the id at a given time.

You can create a separate table for the items that you're identifying items(id PRIMARY KEY, country_code) and have the hypertable be day_ahead_prices(time, value, item_id REFERENCES items(id))