Have you run into this puzzling error? It comes in a couple of flavors, but it boils down to you failing miserably when you run the create_hypertable() function on your PostgreSQL TimescaleDB database server! Annoyingly, it might mean that the whole function is missing (extension is not installed or loaded) or that your parameters are not properly parsed (an issue with your command).
I know, that it sucks. I have been there. That’s why I wrote this article!
You’re trying to transform your PostgreSQL database/schema, possibly already using TimescaleDB, into a Hypertable. However, that fails and throws an error like this:
Npgsql: 42883: function create_hypertable(…) does not exist.
You might get the parameters populated like this:
Npgsql: 42883: function create_hypertable('TableOne','created') does not exist.
Or they might be unknown like this:
Npgsql: 42883: function create_hypertable(unknown, unknown) does not exist.
You’ll either be missing the extension from the server, it might not be loaded properly, it might be missing from your database, or your command might be using bad or corrupted parameters.
Below, I’ll go through the solutions to these issues!
The simple tutorial below explains step-by-step what to do (or verify) to get this working!
Time needed: 20 minutes.
How to configure your PostgreSQL/TimescaleDB to support Hypertables?
- Add Hypertables extension to your database server
Navigate to your PostgreSQL server, select “Server parameters” and then find “shared_preload_libraries” – it should be a dropdown, from which you can enable TIMESCALEDB.
Then navigate back to Overview of your PostgreSQL server and select Restart.
- Add Hypertables extension to your database (schema)
Connect to your PostgreSQL database with psql (or any other such tool), and run the following SQL:
CREATE EXTENSION IF NOT EXISTS timescaledb CASCADE;
I guess technically speaking you could also insert this into an EF Core Migration!
In case you’re wondering how to connect to your Azure PostgreSQL Server with psql, you can do this with Cloud Shell – and it’s really easy! See here:
How to configure Azure PostgreSQL to allow Cloud Shell connections?
- (OPTIONAL) Add an empty EF Core Migration
Do this only if you’re using Entity Framework in the first place. 😅 Just run Add-Migration
- Change a table into a hypertable with a function
create_hypertable() is a function that has a couple of alternative signatures – the one I’ll be using takes the (1) table name first and (2) the column name of the column containing time values (as well as the primary column to partition by).
You will need to call this function, and instead of actually transforming or changing a table into a hypertable, it creates a new hypertable based on your old table – and deletes the old one. This is irreversible!
Additionally, it is a SELECT <function_name> -kinda command. We can live with that – it’ll look somewhat like the below:
SELECT create_hypertable(‘”TableOne”‘, ‘Created’)
Wait, WAIT – what’s with the weird quotes?
Yep, that’s what it takes – the column name needs to be in single quotes, but the table name needs to be first in double quotes (if not in lowercase), then wrapped in single quotes.
- (OPTIONAL) Run create_hypertable in your EF Core Migration
There are a couple of exact ways that you can write this command, but at the end of the day, you’ll probably be using migrationBuilder.Sql to run your SQL commands (in case you’re using EF, that is).
At the end of the day, your migration and command(s) might look somewhat like this:
protected override void Up(MigrationBuilder migrationBuilder)
migrationBuilder.Sql(@"SELECT create_hypertable('""TableOne""', 'Created')");
migrationBuilder.Sql(@"SELECT create_hypertable('""TableTwo""', 'Created')");
Wait, wait, WAIT! This is even weirder now, with different quotes and literal string??
Yeah, it is pretty weird, sure.
The column name needs to be in single quotes, but the table name needs to be first in double-quotes (if not in lowercase), then in single quotes, and finally the double-quotes need to be escaped (for string literal).
I couldn’t get the normal escape (non-literal string with \”) to work, so this was my workaround (using @ to make the string literal)… 😉
And that’s it! Let me know if it works for you as well in the comments -section below! ☺
- Power IoT and time-series workloads with TimescaleDB for Azure Database for PostgreSQL