How to add a new element to an array column in PostgreSQL

Question

Im using PostgreSQL for my application,

The task will be as follows:

There are users who use my application, and I need to maintain a notification for each of them based on their activity, so I get many, many notifications for each user.

so in general we just put all the notifications in a separate table and we make a map with a user id, so we can just get them, but now Im thinking of using either the json / array data type, so I can just put the whole notification specified as array / json, in the cell of each user line.

Which one is better saving json or array? (Im using PHP server side)

and if we select any of them, let's say we have 10 notifications, and I got the 11th number, how to add a new element to the array / json object in a single execution (maybe there is an UPDATE statement)? I don’t want to go basically how to select a string, get an existing / json array, add a new element at the end (process with PHP) and UPDATE it back - it requires two executions, where another change can happen, and that causes dataloss

so is there a way to make an UPDATE query that simply adds a new element or modifies an existing / node element in / json obejct array types in PostgreSQL?

Answer 1

PostgreSQL, || operator array_append.

||

UPDATE table SET array_field = array_field || 'new item' WHERE ...

array_append

UPDATE table SET array_field = array_append(array_field,'new item') WHERE ...

, http://www.postgresql.org/docs/current/interactive/functions-array.html

, json.