PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Cannot create hypertable in timescaleDb

I installed timescaleDb using Docker following this tutorial for the setup: https://docs.timescale.com/self-hosted/latest/install/installation-docker/ . I used this image : timescale/timescaledb-ha:pg14-latest

Then I connected to the instance with pgAdmin and followed this official tutorial: https://docs.timescale.com/getting-started/latest/tables-hypertables/.

CREATE DATABASE "test-timescale";

CREATE TABLE IF NOT EXISTS "Measurements" (
    "Id" SERIAL PRIMARY KEY,
    "DeviceId" VARCHAR(255) NOT NULL,
    "SensorId" VARCHAR(255) NOT NULL,
    "Timestamp" TIMESTAMPTZ NOT NULL,
    "Temperature" DECIMAL(10, 2)
);

However when I try to run the command for creating hypertables I get an error that the by_range function does not exist

SELECT create_hypertable('Measurements', by_range('Timestamp'));

The error:

LINE 1: SELECT create_hypertable('Measurements', by_range('Timestamp...
                                                 ^
HINT: No function matches the given name and argument types. You might need to add explicit type casts.

I checked that I have the timescaleDb extension installed and created. Also tried changing the quotes to doubles, but without success.

What am I missing here?

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    So I have made a couple of mistakes. First of all I had my table named with a capital letter, which just creates additional headaches in Postgresql, where I would need to wrap the variable in double quotes inside single quotes (eg. '"Measurement"'). Secondly I had a primary key constraint added to the Id which made it impossible for the function to create the necessary tables.

    So I have made a workaround using:

    SELECT create_hypertable('measurements', 'Timestamp', if_not_exists => TRUE, chunk_time_interval => INTERVAL '1 day');

    This command now worked and I have succesfully created the hypertable.

    However I am still interested in why the by_range function does not work out of the box like it is described in the documentation. If anyone can clear that up I would be delighted.


  2. 0

    You’re probably trying to use TimescaleDB 2.13+ syntax (the by_range() thing) on an earlier version. You can revert to the old one. Note the case-sensitivity. Demo at db<>fiddle:

    ALTER TABLE "Measurements" DROP CONSTRAINT "Measurements_pkey";
SELECT create_hypertable('"Measurements"', 'Timestamp');

    When running this, "Id" can’t be a primary key, because it would mean it’s unique on its own; Timescale requires that if there’s a unique constraint, the partition key needs to be a part of it:

    To define an index as a UNIQUE or PRIMARY KEY index, the index must include the time column and the partitioning column, if you are using one.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search