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

Question

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?

Answer 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:

• Postgres data type cast

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.