3

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?

3
  • 2
    What error do you get? Jun 29 at 14:35
  • I think the => INTERVAL(...) is the issue. Try => ((SELECT now()::DATE::timestamp - (SELECT min(last_ts) FROM exchange.capture_tracker)))::interval Jun 29 at 14:41
  • 1
    Or just => ((SELECT now()::DATE::timestamp - (SELECT min(last_ts) FROM exchange.capture_tracker))). Timestamp - timestamp should give interval as the output type. Jun 29 at 14:42

1 Answer 1

3

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.

1
  • Ok, I understand; so the parenthesis were making the interpreter think I was calling the function! That has us go in circle for a while. Thanks!
    – Thomas
    Jun 29 at 20:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.