Creating a TimescaleDB hypertable in PostgreSQL with a table that has a numeric name

Viewed 155

In the following, I will present two examples. The first one has a non-numeric name and the second one has a numeric name. The TimescaleDB extension is already added to the database.


1.1 Creating the table:

create table "one"(time timestamp);
CREATE TABLE

1.2 Checking the OID of the table:

SELECT 'one'::regclass::oid;
  oid  
-------
 61962
(1 row)

1.3 Creating the Hypertable:

SELECT create_hypertable('one','time');
NOTICE:  adding not-null constraint to column "time"
DETAIL:  Time dimensions cannot have NULL values.
 create_hypertable 
-------------------
 (1,public,one,t)
(1 row)

2.1 Creating the table:

create table "1"(time timestamp);
CREATE TABLE

2.2 Checking the OID of the table:

SELECT '1'::regclass::oid;
 oid 
-----
   1
(1 row)

2.3 Creating the Hypertable:

SELECT create_hypertable('1','time');
ERROR:  could not open relation with OID 1

One possible solution would be to create the Hypertable with the OID of "1". That seems pretty complicated to me in the long run.

Getting the OID of "one" and "1":

SELECT oid, relname from pg_class where relname='one' or relname='1';
  oid  | relname 
-------+---------
 61959 | 1
 61962 | one
(2 rows)

Creating the hypertable with the OID of "1" as name:

SELECT create_hypertable('61959','time');                 
NOTICE:  adding not-null constraint to column "time"
DETAIL:  Time dimensions cannot have NULL values.
 create_hypertable 
-------------------
 (2,public,1,t)
(1 row)

PostgreSQL seems to be 'confused' as to whether it should look for the name "1" or the OID "1". Can I work with numeric names? How can I create the Hypertable?

1 Answers

If you really wish to commit this atrocity, you can do it by passing the double quotes to timescaledb.

SELECT create_hypertable('"1"','time');
Related