How do I create a hypertable with TimescaleDB from a table with joint Primary Key?

Question

The question pretty much says it all. I'm trying to create a hypertable with TimescaleDB from a table with joint Primary Key:

CREATE TABLE cars
(
    id BIGINT NOT NULL GENERATED ALWAYS AS IDENTITY,
    time_bought TIMESTAMP NOT NULL,
    brand VARCHAR(100),
);


ALTER TABLE cars ADD CONSTRAINT PK_id_time_bought PRIMARY KEY(id, time_bought);


SELECT create_hypertable('cars', 'time_bought');

When i try to run this with Java via Intellij i get this error:

SQL State  : 42883
Error Code : 0
Message    : ERROR: function create_hypertable(unknown, unknown) does not exist
  Hint: No function matches the given name and argument types. You might need to add explicit type casts.
  Position: 8
Location   : db/migration/tenants/V1__init_schema.sql (C:\example\target\classes\db\migration\tenants\V1__init_schema.sql)
Line       : 45
Statement  : SELECT create_hypertable('cars', 'time_bought')

Update: I've tried to run the migration without putting any Primary Keys in the table, and it still gives the same error. Could the problem be that Flyway does not support TimescaleDB functions at all? And if so, how do I work around it?

Answer 1

According to the documentation of create_hypertable the call to it looks to me correct. So it is likely that none of TimescaleDB functions can be found. The common reasons are:

1. TimescaleDB extension was not created in the database.
2. TimescaleDB functions are in different schema than the current schema.

TimescaleDB extension is created per database. Thus if it was created in one database, it will not be available in another database. If the extension was created, can be checked with \dx. For example

\dx
                 List of installed extensions
  Name   | Version |   Schema   |         Description
---------+---------+------------+------------------------------
 plpgsql | 1.0     | pg_catalog | PL/pgSQL procedural language
(1 row)

create extension timescaledb;

\dx
                                       List of installed extensions
    Name     |  Version  |   Schema   |                            Description
-------------+-----------+------------+-------------------------------------------------------------------
 plpgsql     | 1.0       | pg_catalog | PL/pgSQL procedural language
 timescaledb | 2.3.0-dev | public     | Enables scalable inserts and complex queries for time-series data
(2 rows)

Note that the extension was created in the schema public.

So if the session is not in the same schema, e.g., public, then the function will not be found. The current schema can be checked with SELECT current_schema;. If it is not the same schema, then the schema name should be provided in the function call. For example:

SELECT current_schema;
 current_schema
----------------
 test_schema
(1 row)

SELECT create_hypertable('my_table', 'time_column');
ERROR:  function create_hypertable(unknown, unknown) does not exist
LINE 1: SELECT create_hypertable('my_table', 'time_column');
               ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

SELECT public.create_hypertable('my_table', 'time_column');