CTE可用于选择所需的列，然后在CTE上进行过滤，如：

;
WITH my_union_sales AS (
    SELECT
       u.id as UserId,
       p.name as Name,
       p.email as Email,
       'Purchase' as Table,
       p.id as SaleId,
       p.status_id as SaleStatusId,
       p.description as SaleDescription
    FROM users u
    INNER JOIN purchase p on u.id = p.user_id
    
    UNION ALL
    
    SELECT
       u.id as UserId,
       l.name as Name,
       l.email as Email,
       'Lease' as Table,
       l.id as SaleId,
       l.status_id as SaleStatusId,
       CASE
           WHEN l.status_id = 2 THEN 'Finalized Lease'
           ELSE 'Pending Lease'
       END as SaleDescription
    FROM users u
    INNER JOIN lease l on u.id = l.user_id
) SELECT * 
FROM my_union_sales
WHERE (@searchDescription = {} or SaleDescription = any (@searchDescriptions));

您得到该错误的原因是由于logical [order of] query processing与语法规则要求的子句顺序不同。而“选择查询”根据语法以“选择”开始;它不是要执行的第一个子句。获取数据（FROM & JOIN）然后过滤该数据（WHERE）的子句是要执行的第一组子句。
这对您的问题意味着在select子句下建立的列别名是“未知的”，直到考虑select子句，并且ths在where子句之后。因此，您将获得所遇到的错误类型。（* 注：这个列别名未知的问题对于Postgres和许多其他RDBMS都是正确的，但不是全部。*）

/*
    Problem, the column alias "saledescription" is not understood 
    in the where clause of a single select statement.
    this is due to the logical processing order of SQL clauses:
    1.FROM 2.WHERE 3.GROUP BY 4.HAVING 5.SELECT
*/
SELECT
      u.id AS UserId
    , l.name AS Name
    , l.email AS Email
    , 'Lease' AS TABLE
    , CASE WHEN l.status_id = 2 THEN 'Finalized Lease' 
           ELSE 'Pending Lease' END                       AS SaleDescription
FROM users u
INNER JOIN lease l ON u.id = l.user_id
WHERE SaleDescription IN ('Finalized Lease')

> ERROR:  column "saledescription" does not exist
> LINE 16: WHERE SaleDescription IN ('Finalized Lease')

要解决这个问题，有几种方法：

/*
  method 1, repeat the case expression in the where clause
*/
SELECT
      u.id AS UserId
    , l.name AS Name
    , l.email AS Email
    , 'Lease' AS TABLE
    , CASE WHEN l.status_id = 2 THEN 'Finalized Lease' 
           ELSE 'Pending Lease' END                       AS SaleDescription
FROM users u
INNER JOIN lease l ON u.id = l.user_id
WHERE CASE WHEN l.status_id = 2 THEN 'Finalized Lease' 
           ELSE 'Pending Lease' END  
      IN ('Finalized Lease')

/*
  method 2, use a "derived table" (nested subquery)
*/
SELECT *
FROM (
    SELECT u.id AS UserId
        ,l.name AS Name
        ,l.email AS Email
        ,'Lease' AS TABLE
        ,CASE WHEN l.status_id = 2 THEN 'Finalized Lease' ELSE 'Pending Lease' END AS SaleDescription
    FROM users u
    INNER JOIN lease l ON u.id = l.user_id
    ) AS derived
WHERE SaleDescription IN ('Finalized Lease')

/*
  method 3, use a common table expression (CTE)
*/
WITH CTE
AS (
    SELECT u.id AS UserId
        ,l.name AS Name
        ,l.email AS Email
        ,'Lease' AS TABLE
        ,CASE WHEN l.status_id = 2 THEN 'Finalized Lease' ELSE 'Pending Lease' END AS SaleDescription
    FROM users u
    INNER JOIN lease l ON u.id = l.user_id
    )
SELECT *
FROM CTE
WHERE SaleDescription IN ('Finalized Lease')

fiddle