CODEWITHSUNDEEP
amazon-redshift
xmar
xmar

Reputation: 1809

Redshift - SELECT expression works, but that expression is not usable when comparing with table value

I am selecting a value on Redshift that gets me a valid value (the timestamp 7 days before now):

SELECT cast(extract (epoch from (NOW() - INTERVAL '7 days') ) as integer) ;

1651401212

However, I'm trying to use it on a query but fails:

SELECT  * from mytable where my_epoch_timestamp > cast(extract (epoch from (NOW() - INTERVAL '7 days') ) as integer)  ;

ERROR: Specified types or functions (one per INFO message) not supported on Redshift tables.

my_epoch_timestamp column is of type integer, hence the cast on the right side. The type of the separate selections is integer and hence it should be comparable. Am I hitting some kind of limitation here?

Note: I can see that similar approach works in PostgresQL here.

Side note: I see that the value itself would probably be better to be precomputed first to keep it as a fixed value to use here, but not sure if engine should do that already. May that be causing the trouble here?

Upvotes: 0

Views: 741

Answers (1)

Bill Weiner
Bill Weiner

Reputation: 11032

Oh the "ERROR: Specified types or functions (one per INFO message) not supported on Redshift tables." error. This has to be one of the worst written error messages in existence. This error means that the query tried to moved data from the leader node to the compute nodes which is an unsupported path. It can crop up in a number of ways but in your case it is the NOW() function. NOW() is a leader only functions as it returns the time that the transaction began (if memory serves correctly) which is kept on the leader node. GETDATE() is provides the time that the query runs and is available on the compute nodes. If this slight difference in meaning isn't important to your process (and it usually isn't), just replace NOW() with GETDATE() and you should be good to go.

Upvotes: 2

Related Questions