Accessing postgres data structure

Question

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?

Your notation is rather mysterious, what is the type of data? — klin
– klin, Commented Aug 17, 2016 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
– EamonnMcElroy, Commented Aug 17, 2016 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
– klin, Commented Aug 17, 2016 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
– EamonnMcElroy, Commented Aug 17, 2016 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
– EamonnMcElroy, Commented Aug 17, 2016 at 15:49

Community · Accepted Answer · 2017-05-23 12:00:49Z

0

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

postgresql - replace all instances of a string within text field

edited May 23, 2017 at 12:00

CommunityBot

11 silver badge

answered Aug 17, 2016 at 16:06

EamonnMcElroy

6079 silver badges21 bronze badges

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

Collectives™ on Stack Overflow

Accessing postgres data structure

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related