Postgresql-删除表中的括号

yrefmtwq 于 2022-12-12 发布在 PostgreSQL

关注(0)|答案(1)|浏览(242)

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}

postgresql

来源：https://stackoverflow.com/questions/74705087/postgresql-remove-the-brackets-in-the-table

1条答案

按热度按时间

soat7uwm1#

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.

赞(0）回复(0）举报 2022-12-12

我来回答

Postgresql-删除表中的括号

1条答案

相关问题

热门标签

最新问答