postgresql 从查询的多个列中返回第一个正值

Question

我在 POStgreSQL 数据库中有一个表，其中有多个列，其中只有一个将输入值。

    SELECT "Garden_GUID", "Municipality_Amajuba", "Municipality_Ilembe", "Municipality_Sisonke" from forms_garden
WHERE "Garden_GUID" = 'testguid';
 Garden_GUID | Municipality_Amajuba | Municipality_Ilembe | Municipality_Sisonke 
-------------+----------------------+---------------------+----------------------
 testguid    | Dannhauser           |                     | 
(1 row)

我希望创建一个 View ，其中来自这些列的条目被整理到一个单独的列中。

我试过:

CREATE VIEW municipality (GUID,funder,municipality)
AS SELECT "Garden_GUID"GUID,"Funder"funder,"Municipality_Amajuba","Municipality_Ilembe","Municipality_Sisonke"municipality
FROM forms_garden;

但它返回一个错误:

错误:多次指定“自治市”列

有什么方法可以逐行查询各个 municipality_* 列，并且只返回第一个正条目吗？

非常感谢。

Answer 1

我认为coalesce()是你要找的:

with forms_garden as (
    select 'guid1' guid, 'Dannhauser' amajuba, null ilembe, null sisonke
    union all select 'guid2', null, 'muni2', null
    union all select 'guid3', null, null, 'muni3'
) select guid, coalesce(amajuba,ilembe,sisonke) municipality from forms_garden;