how to update string concatenation in postgres sql with existing value

Question

I have a reports table with value as shown below

id  reportIdList
1   123, 124, 125
2   123, 124, 125
3   123, 124, 125, 127
4   123, 124, 125, 127

I need some help with sql to add additional value as in

id  reportIdList
1   123, 124, 125, *126*
2   123, 124, 125, *126*
3   123, 124, 125, *126*, 127
4   123, 124, 125, *126*, 127

Currently I have a way to update

update reports set reportIdList = reportIdList || ',126';

But this would update the table as shown below:

  id  reportIdList

  1   123, 124, 125, *126*
  2   123, 124, 125, *126*
  3   123, 124, 125, 127, *126*
  4   123, 124, 125, 127, *126*

Any help is appreciated, thanks

You shouldn't be storing comma separated lists to begin with. Do you have a chance to fix your data model? — user330315
– user330315, Commented Dec 2, 2019 at 21:09
The table has other values as comma separated lists, but always open to hear suggestions, please do let me know thanks — AnoopN
– AnoopN, Commented Dec 2, 2019 at 21:11
Create a properly normalized one-to-many relationship with a foreign key (and probably a unique index) and this gets as easy as insert into ... — user330315
– user330315, Commented Dec 2, 2019 at 21:13
@AnoopN Yes, using a postgres or json array would make it easier, but still not as easy as a proper relational design. — Bergi
– Bergi, Commented Dec 2, 2019 at 21:19

score 1 · Accepted Answer · 2019-12-02 22:18:19Z

1

The easiest way is to create a function to deal with your bad design:

create or replace function add_element(p_input text, p_add text)
  returns text
as
$$
  select string_agg(x::text, ','  order by x)
  from (
    select trim(nullif(x,''))
    from unnest(string_to_array(p_input, ',')) as e(x)
    union 
    select p_add
  ) t(x);
$$
language sql;

Then you can do:

update the_table
  set reportidlist = add_element(reportidlist, 126);

But you should really fix your data model and stop storing comma separated strings.

edited Dec 2, 2019 at 22:18

answered Dec 2, 2019 at 21:16

user330315

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

5 Comments

AnoopN Over a year ago

The union by default changes the order of the values..and i am sorry in my example i have used only integers but my table report ids are alphanumeric as in the value is like 5811957ba681,eab24d87bb9e,b4e01c134c67 and i wanted to add 187d19223303. Any help with these data??

user330315 Over a year ago

@AnoopN: that's why the string_agg() uses order by - but with text values like that this won't help.

AnoopN Over a year ago

ya the order is all messed up :). Any other way to update this comma separated strings??

user330315 Over a year ago

Having comma separated values in a single column is a bad idea to begin with. But making the order of those elements important, is even worse. If you normalize that to a proper one-to-many relationship, the "many" table doesn't have an order either

AnoopN Over a year ago

thanks, yes, i am working to change the design. Hopefully will be able to correct it. Thanks for your help

Collectives™ on Stack Overflow

how to update string concatenation in postgres sql with existing value

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related