Array_agg and dimensions in Array

From: Mike Martin <redtux1(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Array_agg and dimensions in Array
Date: 2019-01-13 23:05:21
Message-ID: CAOwYNKa-4FCUHe21WwfNSDg9p_-AMVwrW8RitqYonoY1+1+Lqg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I have come across a problem which I cant seem to solve in a nice way
Basically I have a (small) table of tags
What I need to is combine two concatenated fields with a literal value as
an array element.

First thought was using array_agg with a pre-created array as

select array_agg(ARRAY['-metadata',optname||'='||optvalue])) metaopt from
encodeopts
where alias is not null and opttype in ('tag','tagn')
group by transref,fileid ) a

However this results in a multi-dimensional array, rather than a single
dimensioned one, which makes it impossible to join with the rest of an
array created elsewhere in the query

This works, but is very cludgy

select ARRAY['-map_metadata','-1']||array_agg(metaopt) from
(select unnest(array_agg(ARRAY['-metadata',optname||'='||optvalue]))
metaopt from encodeopts
where alias is not null and opttype in ('tag','tagn')
group by transref,fileid ) a

So does this

select
string_to_array(string_agg('-metadata',||'||'||optname||'='||optvalue])),'||')
metaopt from encodeopts
where alias is not null and opttype in ('tag','tagn')
group by transref,fileid

but again cludgy

Any ideas appreciated

Mike

Responses

Browse pgsql-general by date

  From Date Subject
Next Message robert 2019-01-14 07:58:58 the installation of pgadmin4 makes me weep in frustration
Previous Message Zach van Rijn 2019-01-13 19:42:33 Re: Static PostgreSQL Binaries (Linux + Windows)