将日期和整数合并 [英] combine date and integer in ARRAY

问题描述

如何选择 array_agg（ARRAY [f1_date，ARRAY [f2_int，f3_decimal]]）？在数组中组合 date 和 integer 时出错。


更新：添加了图片，解释了我计划在哪里以及如何使用数组。问题是数据库大小。将3个列转换为多维数组后，我可以节省大量空间。这将是4M行，而不是200M行。每行内部最多具有500个元素的数组。

How to select array_agg(ARRAY[f1_date,ARRAY[f2_int,f3_decimal]])? There is an error about combining date and integer in ARRAY.

upd: added picture explaining where and how I plan to use array. The issue is db size. After transforming 3 colunms to multidimensional array I can save plenty of space. It will be 4M rows instead of 200M. Each row will have array with maximum 500 elements inside.

推荐答案

Postgres中的数组在所有维度上共享相同的基本元素。

Arrays in Postgres share the same base element across all dimensions.

您可以构建一个匿名记录数组（作为基本类型）：

You can build an array of anonymous records (as base type):

SELECT array_agg((i,d))
FROM  (
   VALUES
     (1::int, 2.3::decimal)
    ,(2, 3.4)
    ,(3, 4.5)
   ) x(i, d);

但是这相当笨拙，因为您不能按名称访问匿名记录的子字段（名称不存在！）。使用众所周知的类型进行操作可能更实用。

This is rather unwieldy though, as you cannot access subfields of anonymous records by name (names do not exist!). May be more practical to operate with well know types ..

创建复合类型并将其用作数组的基本类型。

Create a composite type and use it as base type for your array.

CREATE TYPE int_dec AS (i int, d decimal);

SELECT '(1, 2.3)'::int_dec AS id_base
      ,'{"(1, 2.3)","(2, 3.4)","(3, 4.5)"}'::int_dec[] AS id_arr

-- Build an array from composite base type
SELECT array_agg(a)
FROM (
    VALUES
      ('(1, 2.3)'::int_dec)
     ,('(2, 3.4)'::int_dec)
     ,('(3, 4.5)'::int_dec)
    ) x(a);

-- Build an array from composite base type
SELECT array_agg((i,d)) AS anonymous_arr
      ,array_agg((i,d)::int_dec) AS id_arr
FROM  (
   VALUES
     (1::int, 2.3::decimal)
    ,(2, 3.4)
    ,(3, 4.5)
   ) x(i, d);

表作为基本类型

任何 table 可以用作复合类型。

Table as base type

Any table can serve as composite type.

db=# CREATE TEMP TABLE int_dec (i int, d decimal);
CREATE TABLE
db=# INSERT INTO int_dec VALUES (1, 2.3), (2, 3.4), (3, 4.5);
INSERT 0 3
db=# SELECT array_agg(id) FROM int_dec id;
            array_agg
---------------------------------
 {"(1,2.3)","(2,3.4)","(3,4.5)"}

Text 作为共同点

另一种方法是将所有值都转换为 text ，因为每个数据类型都可以转换为 text 并返回到PostgreSQL，并构建一个多维数组。

Text as common ground

The alternative is to cast all values to text since every data type can be cast to text and back in PostgreSQL and build a multi-dimensional array.

为此，您可能对汇总多维数组感兴趣。考虑以下相关问题的答案：

将数据选择到Postgres数组

For that you may be interested in aggregating multi-dimensional arrays. Consider the answer under this related question:
Selecting data into a Postgres array

不过，根据我的经验，通常有比构建复杂数组更好的解决方案。

In my experience there is often a better solution than to build complex arrays, though.

这篇关于将日期和整数合并的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！