Postgres Update add to array and if larger than 5 remove last-CodePudding

I have a little program that collects local news headlines all over a country. It should collect the top headline every day in an array and if it has more than 5 headlines, it should remove the oldest one and add the newest one at the top.

Heres the table:

CREATE TABLE place{
     name text PRIMARY KEY,
     coords text,
     headlines json[]
}

The headlines array is basically just json objects with a time and headline property, that would be upserted like this:

insert into place VALUES ('giglio','52.531677;13.381777',
                      ARRAY[
'{"timestamp":"2012-01-13T13:37:27 00:00","headline":"costa concordia sunk"}'
]::json[]) 
ON CONFLICT ON CONSTRAINT place_pkey DO
UPDATE SET headlines = place.headlines || EXCLUDED.headlines

But obviously as soon at it hits 5 elements in the array, it will keep adding onto it. So is there a way to add these headlines and limit them to 5?

Alternative Solution:

insert into place VALUES ('giglio','52.531677;13.381777',
                      ARRAY[
'{"timestamp":"2012-01-13T13:37:27 00:00","headline":"costa concordia sunk"}'
]::json[]) 
ON CONFLICT ON CONSTRAINT place_pkey DO
UPDATE SET headlines = place.headlines[0:4] || EXCLUDED.headlines
RETURNING *

CodePudding user response：

So is there a way to add these headlines and limit them to 5?

I believe yes.

You can define max array size

(search section 8.15.1 here https://www.postgresql.org/docs/current/arrays.html#ARRAYS-DECLARATION)

like this

headlines json[5]

But current implementation of Postgres does not enforce it (still good to do it for future compatibility and proper data model definition).

So I'd try if CHECK constraint is of any help here:

headlines json[5] CHECK (array_length(headlines) < 6)

This should give you a basic consistency check. From here there are two ways to continue (which seems out of the scope of this question):

Catch the PG exception on your app layer, clean up the data, and try inserting it again
Implement a function in your DB schema, that would attempt insert and cleanup.

CodePudding user response：

Here's how I ended up doing it:

insert into place VALUES ('giglio','52.531677;13.381777',
                      ARRAY[
'{"timestamp":"2012-01-13T13:37:27 00:00","headline":"costa concordia sunk"}'
]::json[]) 
ON CONFLICT ON CONSTRAINT place_pkey DO
UPDATE SET headlines = place.headlines[0:4] || EXCLUDED.headlines
RETURNING *