CODEWITHSUNDEEP
postgresqltimescaledb
otiv33
otiv33

Reputation: 45

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?

Upvotes: 1

Views: 2024

Answers (2)

Zegarek
Zegarek

Reputation: 26302

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.

Upvotes: 4

otiv33
otiv33

Reputation: 45

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.

Upvotes: 1

Related Questions