53

Im using PostgreSQL for my application,

The task will be like this

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

so in general we just put all the notifications in a seperate table and we do map with user ID, so we can just fetch them, but now Im thinking to use either json/array datatype, so I can just put the entire notification set as array/json into the cell of each user row.

Which one is better, storing json or array?( Im using PHP at server side)

and if we pick any one among the two, lets say we have some 10 notifications, and I got the 11th one, how do we append the new item into the array/json object in single execution(may be UPDATE statement)? I dont want to go in basic way like select the row, get the existing array/json add the new item in the end(process with PHP) and UPDATE it back- here it takes two executions where another change may occur and that brings dataloss

so is there a way to do UPDATE query that just adds a new element or alters the existing element/node in array/json obejct types in PostgreSQL?

ZAR
  • 2,550
  • 4
  • 36
  • 66
CodeRows
  • 933
  • 1
  • 9
  • 15

1 Answers1

92

To append an item to an array in PostgreSQL you can use the || operator or the array_append function.

With || operator

UPDATE table SET array_field = array_field || '{"new item"}' WHERE ...

With array_append function

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

Also, you can visit this page for array, http://www.postgresql.org/docs/current/interactive/functions-array.html

I don't know how to do it with JSON data type.

George Sovetov
  • 4,942
  • 5
  • 36
  • 57
samed.yildirim
  • 1,048
  • 6
  • 8
  • if you want to use key-value pairs, hstore data type may be better choise for you. – samed.yildirim Mar 28 '15 at 23:05
  • 3
    This works for jsonb (and similarly with json I believe) `update table set array_field = array_field || '{"a":"b"}'::jsonb` – sscarduzio Feb 08 '16 at 13:50
  • 3
    Note for future readers: the solution of @sscarduzio above works (for both json/jsonb) but with the caveat that the column in question must be of datatype JSON/JSONB. It won't work if the column's datatype has been set as array (e.g. int[] or text []). So add the new column as type json(b) and use the commands as outlined in this answer and the comment above. – ahron Nov 15 '21 at 13:22