如何在UNION中使用array_position实现自定义排序？

在PostgreSQL中，UNION 操作的排序逻辑需要作用于整个合并后的结果集，而非单独的子查询；同时你原语句中的数组语法存在错误（双引号用于标识符，字符串应使用单引号）。以下是修正后的实现方式：

正确的视图创建语句 #

CREATE OR REPLACE VIEW public.view
AS
SELECT * FROM (
    SELECT concat('B', bms.id::text) AS id,
           'sample1'::text AS sample_type,
           bms.id AS sample_id,
           NULL::bigint AS other_sample_id,
           bms.sample_type::text as biological_kind
    FROM sample bms
    UNION
    SELECT concat('G', gms.id::text) AS id,
           'sample2'::text AS sample_type,
           NULL::bigint AS sample_id,
           gms.id AS other_sample_id,
           concat(gms.sample_type,'_', bms.sample_type)::text as biological_kind
    FROM other_sample gms
    JOIN sample bms ON bms.id = gms.source_sample
) AS combined_results
ORDER BY array_position(array['Z','A','C','B'], biological_kind);

关键说明 #

• 排序位置调整：将ORDER BY移到外层查询，作用于UNION合并后的完整结果集。UNION的子查询中单独使用ORDER BY（无LIMIT限制）是无效的，PostgreSQL会直接报错，因为子查询的排序对最终合并结果没有意义。
• 数组语法修正：数组内的字符串必须用单引号包裹（'Z'），双引号（"Z"）会被PostgreSQL解析为列名，导致"列不存在"的错误。
• 复用排序规则的优化：如果需要多次使用这个自定义排序逻辑，可以将排序数组封装为一个不可变函数，提升可维护性：

  CREATE OR REPLACE FUNCTION get_biological_sort_order()
  RETURNS text[] AS $$
  BEGIN
      RETURN array['Z','A','C','B'];
  END;
  $$ LANGUAGE plpgsql IMMUTABLE;
  
  -- 调用函数简化视图排序
  CREATE OR REPLACE VIEW public.view
  AS
  SELECT * FROM (
      -- 子查询内容同前
  ) AS combined_results
  ORDER BY array_position(get_biological_sort_order(), biological_kind);
  

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