Postgresql- remove the brackets in the table-CodePudding

I sorted the values of a column, now i want remove the brackets inside {} bracktes.

The following code is for the sorting and updating it in:

FOR _reference, _val IN select reference, categories from responses
LOOP
  _array = (select (array_agg(p order by p.a )) from (select unnest(_val::text[]) as a) as p);
  update responses SET categories = _array where reference = _reference;
END LOOP;

the output of the categories in the table looks like:

{(DSM),(Post)}

I need to that the output looks like:

{DSM,Post}

CodePudding user response：

You are mixing table aliases and column aliases which is the root of your problem.

If you simplify your expression by removing unnecessary levels of nesting and parentheses, things work just fine:

(select array_agg(p.a order by p.a) from unnest(_val::text[]) as p(a))

However you don't need an inefficient PL/pgSQL loop for this. You can do this in a single UPDATE statement.

update responses
  set _val = (select array_agg(p.a order by p.a) from unnest(_val) as p(a))

Or slightly more efficient without array_agg()

update responses
  set _val = array(select p.a from unnest(_val) as p(a) order by p.a)

_val is apparently an array column, so the cast ::text[] seems unnecessary.