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?

The issue is likely related to not finding the function. At least I don't see any other reason for the error from the above code. Call `\dx` to see if TimescaleDB is installed in this database (it needs to be installed in each database). Are you in public schema? — k_rus, May 26 '21 at 19:50
@k_rus But when I run the same function in the Postgres GUI (PGadmin 4), It does not give any error. I'm not in public schema, I'm in a schema called schema_1. — TheStranger, May 27 '21 at 06:13
Try to call the function with specifying the schema: `SELECT public.create_hypertable(...`. TimescaleDB create the functions in `public` by default and thus it will not exist in your schema. — k_rus, May 27 '21 at 07:16
@k_rus That's not the issue, because the migration runs in a specific schema context. But i've found the problem. When i look in 'functions' in my public schema, there is a lot of functions including create_hypertable(), however in schema_1 the 'functions' directory is empty. So i guess i have to put the function into the new schemas when i create them somehow. — TheStranger, May 27 '21 at 07:24
Do you mean that it doesn't work in your environment to provide `public` schema name explicitly when you call the function? — k_rus, May 27 '21 at 07:51
@k_rus I can do that, but does the function not have to be in the specific schema that needs to run it? — TheStranger, May 27 '21 at 07:59
@k_rus Well, apparently not, it works! Thank you very much, my problem is solved. — TheStranger, May 27 '21 at 08:02
Great that it is fixed. I will give an answer later how to deal with such error based on the above discussion. — k_rus, May 27 '21 at 08:16

score 2 · Accepted Answer · answered May 28 '21 at 10:03

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:

TimescaleDB extension was not created in the database.
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');

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

1 Answers1