How to use calculated values ​​in SELECT through AS?

Question

PostgreSQL

Good day!

The question is next. Let's say in SELECT we calculated some value (for example, through a subquery) and display it for the current ROW through some alias. How to use this value in the subsequent sample?

I gave a simple example. Let's say I've subtracted some difference between the date in a particular record's column and the current date. Then, I need to use this value in the CASE construct, but the problem is that the alias name cannot be directly used in the CASE construct (column "minutes_passed" does not exist), although the names of other columns (which are not calculated) can be used.

SET TIME ZONE 'UTC';

SELECT
    payment_id,
    extract(EPOCH from (now() - updated_at::timestamp) / 60) AS minutes_passed,
    CASE
        WHEN 60 - minutes_passed > 0
            AND 60 - minutes_passed <= 15 THEN 'expires'
        WHEN 60 - minutes_passed > 15 THEN 'success'
        ELSE 'expired'
    END
    AS exp_status
FROM public.payments;

Thank you.

Answer 1

CTEs:

WITH p AS (
  SELECT
    payments.*,
    extract(EPOCH from (now() - updated_at::timestamp) / 60) AS minutes_passed
  FROM public.payments
) SELECT 
  p.*,
    CASE
        WHEN 60 - minutes_passed > 0
            AND 60 - minutes_passed <= 15 THEN 'expires'
        WHEN 60 - minutes_passed > 15 THEN 'success'
        ELSE 'expired'
    END
    AS exp_status
FROM p;

PostgreSQL CTE