在PostgreSQL中将复合数组拆分为行和列

Question

在PostgreSQL中将复合数组拆分为行和列

4

Postgres 11.7。

我正在尝试解包一个包含多个元素的数组，但似乎无法正确使用语法。我希望有人能指出我所缺失的部分。以下是一个示例：

select
unnest(array[

                 ('Red Large Special',     1),
                 ('Blue Small',            5),
                 ('Green Medium Special', 87)

              ]) as item_list

这是我想要的：

item_name               item_id  
Red Large Special       1
Blue Small              5
Green Medium Special   87

我得到的内容是：

base_strings
("Red Large Special",1)
("Blue Small",5)
("Green Medium Special",87)

我认为我需要一个列规范列表，类似于这样：

select * from
unnest(array[

                 ('Red Large Special',    1),
                 ('Blue Small',        5),
                 ('Green Medium Special', 87)

              ]) AS item_list(item_name citext, item_id int4)

我得到的是：

ERROR:  function return row and query-specified return row do not match
DETAIL:  Returned type unknown at ordinal position 1, but query expects citext. (Line 9)

如果我正式声明一个自定义的复合类型，就可以让它工作：

CREATE TYPE item_details AS (
   item_name citext,
   item_id   int4);

select * from
unnest(array[

                 ('Red Large Special',    1),
                 ('Blue Small',        5),
                 ('Green Medium Special', 87)

              ]::item_details[]) as item_list

这是正确的：

item_name             item_id
Red Large Special     1
Blue Small            5
Green Medium Special  87

有没有一种方法可以不声明类型而获得相同的结果？我正在寻找一种解决方案，可以在运行时定义类型。我很确定以前在Postgres中做过这件事，但也许是使用JSONB吗？

我已经查阅了关于表返回表达式的Fine文档，但无法理解。那里没有真正的例子，我也无法从语法摘要中推断。

https://www.postgresql.org/docs/current/queries-table-expressions.html

后续操作

两个很棒的答案让我不再打转。在这种情况下，任务是向多个客户端开放某些功能，因此我最好使用JSON而不是Postgres特定的数组语法。@a_horse_with_no_name将我引导到以下代码，从JSON文本开始：

with expanded_data AS (
 select * 
   from json_to_recordset(
        '[
             {"base_text":"Red Large Special","base_id":1},
             {"base_text":"Blue Small","base_id":5},
             {"base_text":"Green Medium Special","base_id":87}
         ]')
      AS unpacked (base_text citext, base_id citext)
 )

select base_text,
       base_id

  from expanded_data

- Morris de Oryx

3个回答

4

我能想到的一种方法是将其转换为jsonb数组：

select item ->> 'f1' as item_name, 
       (item ->> 'f2')::int as item_id 
from jsonb_array_elements(to_jsonb(array[
                 ('Red Large Special',     1),
                 ('Blue Small',            5),
                 ('Green Medium Special', 87)
              ])) t(item)

- user330315

2

我曾遇到同样的问题，在寻找解决方案时，我阅读了这些有用的答案，最终得出结论：

select * from
unnest(array[
    ('Red Large Special'::text,     1),
    ('Blue Small'::text,            5),
    ('Green Medium Special'::text, 87)
]) as item_list(name text, id int)

不需要JSON，也不需要声明类型 ;) Morris de Oryx，你离成功很近了！

请注意，如果没有显式转换为::text，则必须进行转换，否则将会遇到一些麻烦，因为没有带::text的文本将被PostgreSQL引擎解析为未知。

- Victor

那么你确实需要声明一个类型吗？ - alphabetasoup

通过使用我的解决方案，您无需声明和定义新类型。只需记住进行强制类型转换即可。 - Victor

那正是我的观点 :) - alphabetasoup

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Abelisto · Accepted Answer

因为字符串常量实际上具有未知类型，所以您需要明确指定所需的类型：

# select * from
unnest(array[
                 ('Red Large Special'::citext,    1),
                 ('Blue Small'::citext,        5),
                 ('Green Medium Special'::citext, 87)
              ]) AS item_list(item_name citext, item_id int4);
┌──────────────────────┬─────────┐
│      item_name       │ item_id │
├──────────────────────┼─────────┤
│ Red Large Special    │       1 │
│ Blue Small           │       5 │
│ Green Medium Special │      87 │
└──────────────────────┴─────────┘