使用coalesce返回第一个非空值。

-- I have no idea if this lateral join is valid.
LEFT JOIN LATERAL (
  SELECT coalesce(d.country, 'USA')
  FROM db.patient_info d
  WHERE  d.id IN (
    SELECT DISTINCT st.category
    FROM db.surgery_types st, db.surgery_record sr
    WHERE sr.id = st.surgery_record_id AND sr.surgery_type_id = m.id
  )
  ORDER BY d.priority, d.country
  LIMIT 1         
) c ON TRUE

虽然排序依据仍然使用null，所以可能无法正确排序。您可能需要将其拆分为CTE。

-- Again, no idea if the lateral join is valid,
-- just showing a technique.
with countries as(
  SELECT coalesce(d.country, 'USA') as country
  FROM db.patient_info d
  WHERE  d.id IN (
    SELECT DISTINCT st.category
    FROM db.surgery_types st
    JOIN db.surgery_record sr ON sr.id = st.surgery_record_id
    -- Don't know what m is
    WHERE sr.surgery_type_id = m.id
  )
)
with first_country as (
  select country
  from countries
  order by priority, country
  limit 1
)
select
...
LEFT JOIN LATERAL countries on true

最后，更新表以将所有空的国家设置为USA，然后使列为not null可能更简单、更快速。

不考虑您的业务逻辑，也不考虑是否需要横向连接，或者在表达式的选择列表中使用标量子查询就足够了，我的建议如下。

CROSS JOIN LATERAL 
(
 select coalesce
 (
   ( /* your lateral subquery in the brackets here */),
   'USA'
 ) as country
) as c

您不再需要左连接。请注意，这仅在子查询为标量时有效。