Possible inconsistent syntax while referring schema and table with time_bucket?

TimescaleDB and PostgreSQL
1

Hi!

I think I have found a weird syntactic inconsistency while referring the schema and table when using the time_bucket function:

This query below runs properly, as usual:

select
	COUNT (*)
from
	telegraf."UVI"
group by timestamp;

But this one throws an error:

select
	time_bucket('5 minutes', timestamp) as tb,
	COUNT (*)
from
	telegraf."UVI"
group by tb;

SQL Error [42883]: ERROR: function time_bucket(unknown, timestamp without time zone) does not exist
Hint: No function matches the given name and argument types. You might need to add explicit type casts.
Position: 10

Error position: line: 2 pos: 9

However, if I use a Dbeaver console on the needed schema (‘telegraf’) it runs properly:

tb |count|
-----------------------±----+
2022-06-10 06:30:00.000| 6|
2022-05-04 11:55:00.000| 4|
2022-06-08 05:15:00.000| 4|
2022-06-27 12:30:00.000| 3|
2022-05-12 07:10:00.000| 6|
2022-04-08 06:10:00.000| 6|
2022-04-21 14:35:00.000| 4|
2022-06-15 08:30:00.000| 6|
2022-06-06 08:35:00.000| 4|
2022-06-28 10:45:00.000| 4|
2022-04-08 06:20:00.000| 6|
2022-05-19 14:45:00.000| 2|

I believe this is because the hypertable was created by using only the table name instead of the full name (schema.table), is’n it?

What’s the better of the way to fix this issue?

Thank you!

2

Hi @nando

Thanks for the question. I may be wildly off here, but what happens if in the failed query you group by

time_bucket('5 minutes', timestamp) instead of the alias?

1 Like
3

Hi @LorraineP !

It runs properly now!

imagen
imagen723×774 27.8 KB

Thank you!
:ok_hand: :clap:

1 Like
4

Thanks for letting me know!

I wasn’t being flippant by the way, I genuinely didn’t know if that was the problem. But I am super-happy that it has helped :dancer:

1 Like
5

Hi @LorraineP

It was a weird issue. I have the feeling that the way I declared the hypertable (through DBeaver without using the schema name by using a console generated from the schema itself) is a key point ( Is this correct?) but I didn’t expect it were so easily fixed!

I’ll better use pgcli or psql instead of DBeaver until I’ll feel sure with Timescaledb.

Thank you!

6

It is weird because your original approach should work. I’m going to guess it’s to do with DBeaver not recognizing the Timescale bespoke function during its parse so it’s probably out of the control of our engineers, but I’m going to ask the team anyway (and maybe bring it to the attention of the DBeaver devs in case they have an easy way to add the TimescaleDB functions).

Look forward to your next question :star_struck: