Question 89091 in PostgreSQL Question posted in
The official documentation can be found here.

can a CASE expression refer to a different row – Postgresql

I have a product table that has this structure:

  id  product_id phase_type phase_start phase_end
  1    1         Obsolete   2025-01-01  null
  2    1         GA         2022-01-01  null

I want to check if a product is between dates, but in this case phase_end is null, so I want to get the phase_start of Obsolete and use it as if it is the phase end of GA to check whether this product is within a time interval of 6 months before the phase_end:

select 
       id,
       product_id,
       phase_type,
       phase_start,
       phase_end,
       (
        case
          when phase_type = 'GA' 
           and phase_end is null 
           and current_date between (phase_start_of_obsolete - interval '6 month')::date and phase_start_of_obsolete then true
          else false
        end
       ) as current_phase
from table_product

Above the value phase_start_of_obsolete is pseudo-code and indicates i would like to have the phase start date of the same product when that phase is Obsolete. I tried some self joins of the table with itself , but i believe a self join inside a CASE expression might be a very expensive/slow query to make. Is there a better approach to this problem ?

My desired result would look like:

   id product_id phase_type phase_start phase_end current_phase
   1  1          Obsolete   2025-01-01  null      false
   2  1          GA         2022-01-01  null      true
Tags:

2

Answers


  1. 0

    One way to do it is a self-join using an EXISTS clause:

    SELECT t1.*,
       EXISTS (SELECT FROM table_product AS t2
               WHERE t1.phase_type = 'GA'
                 AND t1.phase_end IS NULL
                 AND t2.product_id = t1.product_id
                 AND t2.phase_type = 'Obsolete'
                 AND t2.phase_start - current_timestamp
                        BETWEEN INTERVAL '0-0' AND INTERVAL '0-6'
              ) AS phase_end
FROM table_product AS t1;

 id │ product_id │ phase_type │ phase_start │ phase_end │ phase_end 
════╪════════════╪════════════╪═════════════╪═══════════╪═══════════
  1 │          1 │ Obsolete   │ 2025-01-01  │           │ f
  2 │          1 │ GA         │ 2022-01-01  │           │ f
(2 rows)

    phase_end is currently FALSE for both rows, because it is still 2023.

    Login or Signup to reply.
  2. 0

    The following query returns the requested result without CASE statements or self-joins:

    SELECT
  id,
  product_id,
  phase_type,
  phase_start,
  phase_end,
  phase_type = 'GA'
    AND phase_end IS NULL
    AND daterange(CURRENT_DATE, (CURRENT_DATE + '6 months'::interval)::date) @> min(phase_start) FILTER (WHERE phase_type = 'Obsolete') OVER (PARTITION BY product_id) AS current_phase
FROM
  table_product;

    An alternative form using BETWEEN instead of a multirange is:

    SELECT
  id,
  product_id,
  phase_type,
  phase_start,
  phase_end,
  phase_type = 'GA'
    AND phase_end IS NULL
    AND min(phase_start) FILTER (WHERE phase_type = 'Obsolete') OVER (PARTITION BY product_id) BETWEEN CURRENT_DATE AND CURRENT_DATE + INTERVAL '6' MONTH AS current_phase
FROM
  table_product;

    Instead of checking against a 6 month range, it might be sufficient to simply determine that the current date is later than 6 months prior to the beginning of the "Obsolete" phase.

    A CASE expression isn’t needed because the logical value can be directly evaluated. In general, whenever a logical value is required, avoid using CASE and explicitly setting the value: it is unnecessarily verbose and creates additional opportunities to introduce code defects.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search