PostgreSQL中如何判断AS别名main_bundle_status是否为空并返回结果列？

Hey there! Let's fix that dependent column issue step by step.

First, let's break down why your original CASE statement might be throwing an error: it's likely you're referencing the table alias main_bundle_status instead of the actual column name (like main_bundle_status.status) when checking for nulls. Plus, PostgreSQL has a cleaner way to handle boolean checks for null values without needing a full CASE statement.

Correct Implementation Options #

Let's start with a cleaned-up version of your query, then add the dependent column properly.

Option 1: Use Direct Boolean Expression (Most Concise)

PostgreSQL treats column IS NULL as a boolean value directly, so you can skip the CASE statement entirely:

select
    main_bundle_status.status,
    -- This directly returns TRUE if status is null, FALSE otherwise
    main_bundle_status.status is null as dependent
from (
    select 
        main_bundle_statuses.status 
    from ( 
        select 
            main_bundle.status, 
            row_number() over (partition by main_bundle.client_id order by main_bundle.created desc) as rnum 
        from bundle main_bundle 
        join config_dependence cd on cd.main_config_id = main_bundle.config_bundle_id 
        where cd.dependent_config_id = b.config_bundle_id and main_bundle.client_id = b.client_id
    ) main_bundle_statuses 
    where main_bundle_statuses.rnum = 1
) as main_bundle_status

Option 2: Explicit CASE Statement (If You Prefer Verbosity)

If you still want to use CASE for clarity, make sure you target the correct column:

select
    main_bundle_status.status,
    case 
        when main_bundle_status.status is null then true 
        else false 
    end as dependent
from (
    select 
        main_bundle_statuses.status 
    from ( 
        select 
            main_bundle.status, 
            row_number() over (partition by main_bundle.client_id order by main_bundle.created desc) as rnum 
        from bundle main_bundle 
        join config_dependence cd on cd.main_config_id = main_bundle.config_bundle_id 
        where cd.dependent_config_id = b.config_bundle_id and main_bundle.client_id = b.client_id
    ) main_bundle_statuses 
    where main_bundle_statuses.rnum = 1
) as main_bundle_status

Bonus: Handle "No Matching Records" Scenarios #

If you're expecting main_bundle_status.status to be null when there are no matching rows (right now your inner join filters those out), switch to a LEFT JOIN in the subquery to preserve those rows:

select
    b.*,
    coalesce(main_bundle_status.status, 'No Status') as main_bundle_status,
    main_bundle_status.status is null as dependent
from bundle b
left join (
    select
        main_bundle.client_id,
        main_bundle.status,
        row_number() over (partition by main_bundle.client_id order by main_bundle.created desc) as rnum
    from bundle main_bundle
    join config_dependence cd 
        on cd.main_config_id = main_bundle.config_bundle_id
        and cd.dependent_config_id = b.config_bundle_id
) main_bundle_statuses 
    on main_bundle_statuses.client_id = b.client_id
    and main_bundle_statuses.rnum = 1

内容的提问来源于stack exchange，提问作者Maksym Rybalkin