时间戳

首页 > 解决方案 > 在 PostgreSQL 中将行转换为数组

postgresql - 在 PostgreSQL 中将行转换为数组

问题描述

我需要查询一个表，如

SELECT *
FROM table_schema.table_name

只有每一行需要是一个TEXT[]与列值对应的数组值，该列值以TEXT相同的顺序出现，因此SELECT *假设表有列a，我需要结果看起来像bc

SELECT ARRAY[a::TEXT, b::TEXT, c::TEXT]
FROM table_schema.table_name

只是它不应该按名称显式列出列。理想情况下它应该看起来像

SELECT as_text_array(a)
FROM table_schema.table_name AS a

我想出的最好的看起来很丑，并且依赖于“hstore”扩展

WITH columnz AS ( -- get ordered column name array
    SELECT array_agg(attname::TEXT ORDER BY attnum) AS column_name_array
    FROM pg_attribute
    WHERE attrelid = 'table_schema.table_name'::regclass AND attnum > 0 AND NOT attisdropped
)
SELECT hstore(a)->(SELECT column_name_array FROM columnz)
FROM table_schema.table_name AS a

我有一种感觉，必须有一种更简单的方法来实现这一点

更新 1

另一个实现相同结果但可以说与第一个查询一样丑陋和低效的查询受到@bspates答案的启发。它可能效率更低，但不依赖于扩展

SELECT r.text_array
FROM table_schema.table_name AS a
    INNER JOIN LATERAL ( -- parse ROW::TEXT presentation of a row
        SELECT array_agg(COALESCE(replace(val[1], '""', '"'), NULLIF(val[2], ''))) AS text_array
        FROM regexp_matches(a::text, -- parse double-quoted and simple values separated by commas
            '(?<=\A\(|,)  (?:  "(  (?:[^"]|"")*  )"  |  ([^,"]*)  )  (?=,|\)\Z)', 'xg') AS t(val)
    ) AS r ON TRUE

离理想还很远

更新 2

我测试了目前存在的所有 3 个选项

  1. 使用JSON. 不依赖任何扩展，写的很短，通俗易懂，速度还可以。
  2. 使用hstore. 这种替代方法是最快的（比 100K 数据集上的方法快 10 倍以上JSON），但需要扩展。hstore一般来说，通过扩展是非常方便的。
  3. 用于解析ROWregex的 TEXT 表示。这个选项真的很慢。

标签： postgresql

解决方案

一个有点丑陋的 hack 是将行转换为 JSON 值，然后取消嵌套值并将其聚合回数组：

select array(select (json_each_text(to_json(t))).value) as row_value
from some_table t

这在某种程度上与您的 hstore hack 相同。

如果列的顺序很重要，那么使用jsonandwith ordinality可以用来保持：

select array(select val 
             from json_each_text(to_json(t)) with ordinality as t(k,val,idx) 
             order by idx)
from the_table t

推荐阅读