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

Postgresql – How to avoid this repetitive CASE WHEN pattern in postgres query?

I have this postgres query that is validating if the columns are null or not based on one codition, this is a normal query that is not requiring function, could be overkill a functional approach because this is unique scenario in the collection.

The query is working as expected but I want to shrink the logic if is possible.

CASE WHEN LOWER(COALESCE(users.user_number, users.customer_number)) LIKE 'vip%' THEN NULL
    ELSE COALESCE(users.user_badge, users.customer_badge) END                              AS normal_badge,
CASE WHEN LOWER(COALESCE(users.user_number, users.customer_number)) LIKE 'vip%' THEN NULL
    ELSE users.expiration_date END                                                         AS expiration_date,
CASE WHEN LOWER(COALESCE(users.user_number, users.customer_number)) LIKE 'vip%' THEN NULL
    ELSE COALESCE(users.user_number, users.customer_number) END                            AS standard_user,
Tags:

2

Answers


  1. 0

    Try this using CTE:

    WITH user_data AS (
    SELECT *,
           LOWER(COALESCE(user_number, customer_number)) AS lower_number
    FROM users
)
SELECT 
    CASE WHEN lower_number LIKE 'vip%' THEN NULL
         ELSE COALESCE(user_badge, customer_badge) 
    END AS normal_badge,
    CASE WHEN lower_number LIKE 'vip%' THEN NULL
         ELSE expiration_date 
    END AS expiration_date,
    CASE WHEN lower_number LIKE 'vip%' THEN NULL
         ELSE COALESCE(user_number, customer_number) 
    END AS coalesced_number
FROM user_data;
    Login or Signup to reply.
  2. 0

    You can pull the information out to a generated column or consider storing it in a separate one in the first place: Demo at db<>fiddle

    alter table users add column is_vip boolean generated always as 
(COALESCE(users.user_number, users.customer_number) ILIKE 'vip%') stored;

    Note that you can use ilike instead of lower() like lower() to make it case-insensitive.

    If you flip the check in your case when (condition) then null else (field) end construct, you can save a few characters. case when not(condition)then(field)end achieves the same because a missing else leads to null.

    case when not is_vip then COALESCE(users.user_badge, users.customer_badge) end AS normal_badge,
case when not is_vip then users.expiration_date end AS expiration_date,
case when not is_vip then COALESCE(users.user_number, users.customer_number) end

    You can also attach sets of fields with a left join lateral, all of which you nullify at once using a single join condition:

    select * from users
left join lateral(
  select COALESCE(users.user_badge, users.customer_badge) as normal_badge,
         users.expiration_date,
         COALESCE(users.user_number, users.customer_number) as normal_number
) on not is_vip;

    select * from users
left join lateral(
  select COALESCE(users.user_badge, users.customer_badge) as normal_badge,
         users.expiration_date,
         COALESCE(users.user_number, users.customer_number) as normal_number
) on not (COALESCE(users.user_number, users.customer_number) ILIKE 'vip%');

    Note that in all cases, if you don’t have vip prefix in non-null user_number, but you do in customer_number, the user isn’t considered vip: coalesce picks and check only the first one.

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

Back To Top
Search