I can't understand a syntax error issue in SQL / TimeScaleDB

All we need is an easy explanation of the problem, so here it is.

This does NOT compile:

SELECT add_compression_policy(
    hypertable => 'exchange.candles',
    compress_after => INTERVAL ((SELECT now()::DATE::timestamp - (SELECT min(last_ts) FROM exchange.capture_tracker))),
    if_not_exists => TRUE);

The issue seems to be the SELECT following INTERVAL.

But this compiles:

WITH delay AS (SELECT now()::DATE::timestamp - (SELECT min(last_ts) FROM exchange.capture_tracker) myinterval)
SELECT add_compression_policy(
    hypertable => 'exchange.candles',
    compress_after => myinterval,
    if_not_exists => TRUE) FROM delay;

My colleague and I can’t understand why.
Can anyone explain?

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

Explanation

The function interval() does not do what you seem to think. You can use the type name like this to cast a value (typed or untyped):

test=> SELECT interval '5 days 20:32:32.06';
      interval      
--------------------
 5 days 20:32:32.06

But you cannot use it as function (followed by parentheses). That’s only implemented for some basic types. See:

There are two functions named "interval" registered in Postgres 14, one taking time, the other interval and integer. Not what you want:

test=> SELECT (proargtypes::regtype[])[:] FROM pg_proc WHERE proname = 'interval';
        proargtypes         
----------------------------
 {"time without time zone"}
 {interval,integer}

You seem to want a cast, but you don’t need that to begin with, as the result of your convoluted expression is interval already.

Solution

Untangled:

SELECT add_compression_policy(hypertable     => 'exchange.candles'
                            , compress_after => (SELECT current_date - min(last_ts) FROM exchange.capture_tracker)
                            , if_not_exists  => TRUE);

now()::DATE::timestamp burns down to CURRENT_DATE. The latter is date instead of timestamp, but the result of the expression is the same. (Either depends on the timezone setting of your current session.)

Or, even simpler:

SELECT add_compression_policy(hypertable     => 'exchange.candles'
                            , compress_after => current_date - min(last_ts)
                            , if_not_exists  => TRUE)
FROM   exchange.capture_tracker;

Since the only reference to the table is min(last_ts), this always returns a single row, even if the table is empty. And the result is the same as for the above.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Tags: , ,

Leave a Reply