Timescaledb: Cannot Create Hypertable on Table with Capital Letters

[timolson]

I've tried "everything"

cointrader=# \dt "Trade"
         List of relations
 Schema | Name  | Type  |  Owner   
--------+-------+-------+----------
 public | Trade | table | postgres
(1 row)

cointrader=# SELECT create_hypertable("Trade"::regclass);
ERROR:  column "Trade" does not exist
LINE 1: SELECT create_hypertable("Trade"::regclass);
                                 ^

cointrader=# SELECT create_hypertable('Trade'::regclass);
ERROR:  relation "trade" does not exist
LINE 1: SELECT create_hypertable('Trade'::regclass);
                                 ^

cointrader=# SELECT create_hypertable("Trade");
ERROR:  column "Trade" does not exist
LINE 1: SELECT create_hypertable("Trade");
                                 ^

cointrader=# SELECT create_hypertable('Trade');
ERROR:  function create_hypertable(unknown) does not exist
LINE 1: SELECT create_hypertable('Trade');
               ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

[RobAtticus]

I believe you have to use '"Trade"'

[timolson]

I tried lowercasing all my table names just to progress, but it doesn't help. Perhaps there is a deeper problem, and I'm at a loss:

cointrader=# CREATE EXTENSION IF NOT EXISTS timescaledb CASCADE;
NOTICE:  extension "timescaledb" already exists, skipping
CREATE EXTENSION
cointrader=# select create_hypertable('ohlcv');
ERROR:  function create_hypertable(unknown) does not exist
LINE 1: select create_hypertable('ohlcv');
               ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
cointrader=# \dt ohlcv
         List of relations
 Schema | Name  | Type  |  Owner   
--------+-------+-------+----------
 public | ohlcv | table | postgres
(1 row)

cointrader=# \dx
                                      List of installed extensions
    Name     | Version |   Schema   |                            Description                            
-------------+---------+------------+-------------------------------------------------------------------
 plpgsql     | 1.0     | pg_catalog | PL/pgSQL procedural language
 timescaledb | 0.12.1  | public     | Enables scalable inserts and complex queries for time-series data
(2 rows)

cointrader=# CREATE EXTENSION timescaledb CASCADE;
ERROR:  extension "timescaledb" has already been loaded with another version
DETAIL:  The loaded version is "0.12.1".
HINT:  Start a new session and execute CREATE EXTENSION as the first command. Make sure to pass the "-X" flag to psql.

Note: I have completely dropped and re-created the database for this test, and I'm using your Docker image with no modifications or extra layers.

[RobAtticus]

Sorry, I just realized you are not including a second argument for which column is the time column so it's not finding the function.

[timolson]

OMG I'm an idiot. Thanks, Rob, and sorry to waste your time. PEBKAC, RTFM.

For those watching this thread, the nested-quote syntax does indeed work for tables with capital letters:

cointrader=# select create_hypertable('OHLCV', 'time');
ERROR:  relation "ohlcv" does not exist
LINE 1: select create_hypertable('OHLCV', 'time');
                                 ^
cointrader=# select create_hypertable('"OHLCV"', 'time');
 create_hypertable 
-------------------

(1 row)

[timolson]

One additional note: columns require single-quotes regardless of capitalization:

select create_hypertable('"TableWithCaps"', 'ColumnWithCaps');

[SumanDas3001]

The problem is that when I want to convert a simple Postgresql table to timescaledb table or hyper table then it will show this error. The table name is orders. Here cas_admin_db_new is the databse name.

I have tried all the possible way. which is bellow but the orders table doesn't convert into hypertable.

SELECT create_hypertable('orders','created_at', chunk_time_interval => 6040800000000);
ERROR: cannot create a unique index without the column "created_at" (used in partitioning)

SELECT create_hypertable('public.orders','created_at', chunk_time_interval => 6040800000000);
ERROR: cannot create a unique index without the column "created_at" (used in partitioning)

cas_admin_db_new=# SELECT create_hypertable('public.orders','created_at', chunk_time_interval => 6040800000000, created_default_indexes=>FALSE);
ERROR: function create_hypertable(unknown, unknown, chunk_time_interval => bigint, created_default_indexes => boolean) does not exist

cas_admin_db_new=# SELECT create_hypertable('"ORDER"','created_at', chunk_time_interval => 6040800000000);
ERROR: relation "ORDER" does not exist
LINE 1: SELECT create_hypertable('"ORDER"','created_at', chunk_time_...

[hackedaccount]

you need to install timescaledb extension in database you are using currently.