How to solve “Npgsql: 42883: function create_hypertable(…) does not exist”?

Time needed: 20 minutes

How to configure your PostgreSQL/TimescaleDB to support Hypertables?

1. 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.

2. Add Hypertables extension to your database (schema)

   Connect to your PostgreSQL database with psql (or any other such tool), and run the following SQL:
   \c "YourSchema"
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?

3. (OPTIONAL) Add an empty EF Core Migration

   Do this only if you’re using Entity Framework in the first place. 😅 Just run Add-Migration

4. 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.

5. (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)… 😉