How to store a temporary value in a Postgresql query and then use it multiple times?

Question

I have this simple query:

SELECT COUNT(*)
FROM CompressorAlerts
WHERE CAST('2020-09-20' as timestamp) IS NULL OR faultTimestamp >= CAST('2020-09-20' as timestamp)

(I hardcoded 2020-09-20 ... it is really a value I am getting from somewhere else, irrelevant to this question, but I hardcoded it for simplicity).

As you can see, I am repeating twice CAST('2020-09-20' as timestamp). I want to avoid that, so I tried doing this:

WITH myDate as (SELECT CAST('2020-09-20' as timestamp))
SELECT COUNT(*)
FROM CompressorAlerts
WHERE myDate IS NULL OR faultTimestamp >= myDate

However, I get this error: column "mydate" does not exist.

How can I reference the myDate value I defined in the WITH clause? Am I doing something wrong?

If the date is actually a parameter then why can't you use where ? is null or faulttimestamp >= ?(or whatever parameter placeholder you use) — user330315
– user330315, Commented Oct 21, 2020 at 14:11

user330315 · Accepted Answer · 2020-10-21 14:10:34Z

3

mydate is the name of the CTE, not the name of the column expression inside it. Additionally you need to include the CTE in the FROM part of the query.

WITH params (mydate) as (
  values (timestamp '2020-09-20')
)
SELECT COUNT(*)
FROM CompressorAlerts, params
WHERE mydate IS NULL 
   OR faultTimestamp >= mydate

answered Oct 21, 2020 at 14:10

user330315

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to store a temporary value in a Postgresql query and then use it multiple times?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related