change brackets for array aggregation in postgres

Question

I like to change the brackets "{" and "}" to "[" and "]" in the following example. I have a table A with two coloumns, one is text_1 of type string and the second is count of type bigint. What I'm trying to do is to return a matrix notation like [[1,2,4],[2,4,5],...].

CREATE AGGREGATE array_agg_mult(anyarray) (
    SFUNC = array_cat,
    STYPE = anyarray,
    INITCOND = '{}'
);

WITH B AS(
SELECT 
    array_agg(count) AS count 
FROM
    A 
GROUP BY
    text_1
)
SELECT
    array_agg_mult(ARRAY[count]) 
FROM
    B;

Besides how to update array_agg_mult, if I try to change INITCOND = '{}' to INITCOND = '[]' I get the

ERROR: function "array_agg_mult" already exists with same argument types

Maybe there is a smart solution by using json generation with postgres.

user330315 · Accepted Answer · 2016-06-02 09:28:28Z

11

Those brackets have nothing to do with the aggregation. Those are just the output format of the array type. A new aggregate function won't change that.

You can verify that by simply doing a

select array[1,2,3]

which will display:

array  
-------
{1,2,3}

The '{}' in the INITCOND simply means "empty array".

See the manual for details: https://www.postgresql.org/docs/current/static/arrays.html#ARRAYS-IO

The only way to change that display would be to change the output method for arrays in general. I don't think this can be done without hacking the Postgres sources (and which would probably break many things)

If you want to display arrays in a different format, write your own function to do that.

create function format_array(p_array anyarray)
  returns text
as
$$
  select translate(p_array::text, '{}', '[]');
$$
language sql;

Then you can use that on any array:

select format_array(array[1,2,3]), 
       format_array('{}'::int[]), 
       format_array(array[[1,2,3],[4,5,6]]);

will output:

format_array | format_array | format_array     
-------------+--------------+------------------
[1,2,3]      | []           | [[1,2,3],[4,5,6]]

Of course you can apply more "cosmetics" in the format_array() function then just replacing {} with []

Your query would then become:

WITH B AS
(
  SELECT array_agg(count) AS count 
  FROM A 
  GROUP BY text_1
)
SELECT format_array(array_agg_mult(ARRAY[count]))
FROM B;

answered Jun 2, 2016 at 9:28

user330315

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

5 Comments

StellaMaris Over a year ago

thanks for advance answer. As you said, that the brackets are only for display the array. How to create a result which uses "[" and "]" instead of "{" and "}". Can I avoid to create the format_array function by using some other functions, e.g. ` array_to_json` also returns a string sourounding with brackets.

user330315 Over a year ago

@StellaMaris: you always need some function to format the output. What's wrong with creating your own function that formats everything you want it?

StellaMaris Over a year ago

There is nothing wrong, I just asking for existing function with desired output. Back to your function, I don't really understand the parameters p_array anyarray in the function header. Is p_array the type of anyarray? I couldn't find any documentation about it.

user330315 Over a year ago

p_array is the name of the parameter: postgresql.org/docs/current/static/…

StellaMaris Over a year ago

I see and anyarray is the type. Tahnks.

mptrossbach · Accepted Answer · 2022-12-13 02:38:38Z

0

You can use the builtin function array_to_json to format an array to have square brackets e.g.

with
data as (
    select array[
        array[1,2,3],
        array[4,5,6],
        array[7,8,9]
    ] as a
)
select
    a as array_original,
    array_to_json(a) as array_formatted
from data;

/*
Returns:
array_original : {{1,2,3},{4,5,6},{7,8,9}}
array_formatted: [[1,2,3],[4,5,6],[7,8,9]]
*/

answered Dec 13, 2022 at 2:38

mptrossbach

3843 silver badges14 bronze badges

Collectives™ on Stack Overflow

change brackets for array aggregation in postgres

2 Answers 2

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related