CODEWITHSUNDEEP
postgresqlpostgresql-9.2
CodeRows
CodeRows

Reputation: 1033

How to append a new item into the array-type column in PostgreSQL

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?

Upvotes: 62

Views: 64951

Answers (1)

samed.yildirim
samed.yildirim

Reputation: 1298

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.

Upvotes: 117

Related Questions