CODEWITHSUNDEEP
postgresql
AnoopN
AnoopN

Reputation: 39

how to update string concatenation in postgres sql with existing value

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

Upvotes: 0

Views: 1193

Answers (1)

user330315
user330315

Reputation:

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.

Upvotes: 1

Related Questions