postgresql malformed array literal in create aggregate initcond

Question

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})'
);

Which gives me

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

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.

404 · Accepted Answer · 2019-06-19 23:28:17Z

1

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

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}')

Returns:

(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:

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.

edited Jun 19, 2019 at 23:28

answered Jun 19, 2019 at 21:50

404

8,7522 gold badges33 silver badges53 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Kassym Dorsel Over a year ago

Obviously the one thing I did not try :/. I guess the don't use double quotes in postgres thought prevented me from trying it... And why does {} or {5} work without having to quote it?

404 Over a year ago

@KassymDorsel Updated answer

manoj Over a year ago

@404 How would we represent an array of a customType, which itself contains a text field and a text array? type is: create type user as (name text, phone_nums text[]), and value I am passing as an array of users is: '{"(abc,{12312345})","(def,{12312345,78956789})"}'

Collectives™ on Stack Overflow

postgresql malformed array literal in create aggregate initcond

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related