创建聚合initcond中的PostgreSQL格式错误的数组文字 [英] postgresql malformed array literal in create aggregate initcond

问题描述

运行postgres 11.3。这是SQL代码

Running postgres 11.3. Here's the sql code

create type _stats_agg_accum_type AS (
    cnt bigint,
    min double precision,
    max double precision,
    m1 double precision,
    m2 double precision,
    m3 double precision,
    m4 double precision,

    q double precision[],
    n double precision[],
    np  double precision[],
    dn  double precision[]
);

create aggregate stats_agg(double precision) (
    sfunc = _stats_agg_accumulator,
    stype = _stats_agg_accum_type,
    finalfunc = _stats_agg_finalizer,
    combinefunc = _stats_agg_combiner,
    parallel = safe,
    initcond = '(0,,, 0, 0, 0, 0, {}, {1,2,3,4,5}, {1,2,3,4,5}, {0,0.25,0.5,0.75,1})'
);

哪个给我

ERROR:  malformed array literal: "{1"
DETAIL:  Unexpected end of input.
SQL state: 22P02

空数组文字可以正常工作。我还尝试了一个单元素文字 {1} ，它可以正常工作。每当我有2个或更多元素时，都会出现此错误。

The empty array literal works ok. I've also tried a one element literal {1} which works fine. Whenever I have 2 or more elements it gives me this error.

作为解决方法，我可以传入空数组，并在第一遍初始化它们，但这很丑陋。

As a work around I could pass in empty arrays and initialize them on the first pass, but that's ugly.

推荐答案

您需要在数组周围加引号，这是因为数组位于文本行中。

You need quotes around your arrays, and that's because the array is in a text version of a row.

通过将您的输入作为一行来进行测试很容易，并查看postgres如何对其进行格式化（此处需要在数组周围使用单引号，因为 {} 是文本数组）

Easy to test by taking your input as a row and see how postgres formats it (single quotes needed around arrays here because {} is an array in text):

SELECT ROW(0,NULL,NULL, 0, 0, 0, 0, '{}', '{1,2,3,4,5}', '{1,2,3,4,5}', '{0,0.25,0.5,0.75,1}')

返回值：

(0,,,0,0,0,0,{},"{1,2,3,4,5}","{1,2,3,4,5}","{0,0.25,0.5,0.75,1}")

因此，您需要执行以下操作：

Therefore you need to do:

...
initcond = '(0,,,0,0,0,0,{},"{1,2,3,4,5}","{1,2,3,4,5}","{0,0.25,0.5,0.75,1}")'

为什么在一个为空或只有一个值的数组上不需要加引号：

Why quotes are not required on an array which is empty or has only one value:

数组中的多个值用逗号分隔，并且a中的字段行也以逗号分隔。如果您将行提供为'（0，{1,2}）'，则PG会将其解释为三个字段： 0 ， {1 ， 2} 。当然，在这种情况下，您会收到有关格式错误的数组的错误。将字段放在引号中意味着这些引号内的所有内容都是一个字段。因此'（0， {1,2}）'将正确解释为 0 ， {1,2} 。如果数组为空或仅包含一个值，则不会有逗号，因此正确解析该字段没有问题。

Multiple values in an array are comma-delimited, and fields within a row are also comma-delimited. If you supply a row as '(0,{1,2})', PG will interpret this as three fields: 0, {1, 2}. Naturally in that case you'll get an error about a malformed array. Putting a field in quotes means everything within those quotes is one field. Therefore '(0,"{1,2}")' will be interpreted correctly as 0, {1,2}. If the array is empty or contains only one value, there will be no comma, so there is no problem parsing that field correctly.

这篇关于创建聚合initcond中的PostgreSQL格式错误的数组文字的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！