时间戳

首页 > 解决方案 > 如何将postgres中的可变长度记录转换为数组？

sql - 如何将postgres中的可变长度记录转换为数组？

问题描述

我正在尝试动态创建表并将它们的列转换为数组。所以我最终会得到一个只有 1 列的数组类型的表。数组的长度将等于输入中的列数。如果我事先知道我的（动态创建的）表有哪些列，解决方案会很简单，但我不知道这一点。

我试过使用数组函数。如果您事先知道表包含哪些列，则此方法有效。如果您不知道这一点（因为表是动态定义的），您必须使用星号来获取所有列。但是数组函数然后创建一个只有 1 个元素的数组，包含所有列。这不是我想要的。

CREATE TEMPORARY TABLE my_addresses(
  personname TEXT NOT NULL, 
  streetname TEXT NOT NULL, 
  country_id TEXT
);
------------------------------------------------------------------------
INSERT INTO my_addresses 
VALUES(  'Bill Gates', 'Somewhere Avenue', 'US'),
        ('Linus Thorvalds', 'Other Road', 'US'),
        ('Erwin Brandstetter', 'Solution Street', 'AT'); --it's a tribute :-)
------------------------------------------------------------------------
SELECT 
    cardinality(ARRAY[  ma.personname, 
                        ma.streetname, 
                        ma.country_id]), --an array of 3 elements is created, as you would expect
    cardinality(ARRAY[ma.*]) --an array of 1 element is created, which is not what I want
FROM my_addresses   AS ma;

顺便说一句，这是我在 stackoverflow 上的第一篇文章（请原谅我的任何格式错误）。我使用 Postgres 版本 9.6.14。

标签： sqlarrayspostgresqlrecord

解决方案

这似乎很好奇。但与涉及整个记录的许多挑战一样，解决方案通常是使用JSON. 在这种情况下：

SELECT (SELECT array_agg(t.v)
        FROM jsonb_each_text(to_jsonb(ma.*)) t(k, v)
       ) as myarray
FROM my_addresses ma;

这是一个 db<>fiddle。

无论如何，您可能会发现 JSON 是解决您最终问题的更好解决方案。但是这个问题特别询问了数组。

推荐阅读