0

I have a table in my postgres table which has data structured strangely. Here is an example of the data structure:

id         |        1
name       |        name
data       |        :type: information
           |        :url: url
           |        :platform: 
           |          android: ''
           |          iphone: ''
created_at |        2016-07-29 11:39:44.938359
updated_at |        2016-08-22 12:24:32.734321

How do i change data > platform > android for example?

EamonnMcElroy
  • 587
  • 6
  • 20
  • Your notation is rather mysterious, what is the type of `data`? – klin Aug 17 '16 at 14:05
  • Sorry. Data type is `text`. Its a strange data structure. I don't think if I have to update a single value e.g. android I would have to output the whole data contents again including the new value ie `update table set data = ':type: information :url: url :platform: android: 'new_value' iphone: ''` I know this isnt the correct way but you get my point. Thanks. – EamonnMcElroy Aug 17 '16 at 14:12
  • Unfortunately, Postgres has no built-in means to update parts of text, so you have to format whole new value manually (or in your application). – klin Aug 17 '16 at 14:26
  • Thanks for the info. Seems to be bad database design. Will be chatting to my colleagues tomorrow and add any further information I might get. Thanks. – EamonnMcElroy Aug 17 '16 at 15:42
  • Just realised I can run replace. Mightnt have been obvious as the data for android was empty but can run `update table set field = replace(field, 'android: ''''', 'android: ''new_value''')`. Does that seem okay to you? – EamonnMcElroy Aug 17 '16 at 15:49

1 Answers1

0

Just did some more research and found this which did the trick:

postgresql - replace all instances of a string within text field

Community
  • 1
  • 1
EamonnMcElroy
  • 587
  • 6
  • 20