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

Question

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 *

Unrelated to the question, but in my opinion jsonb[] hardly ever makes sense. Using jsonb with a "native" JSON array inside is usually the better choice. — user330315
– user330315, Commented Nov 15, 2021 at 12:01
well its json, not jsonb. But doesn't that making merging, operating on, indexing and querying arrays harder? — user2741831
– user2741831, Commented Nov 15, 2021 at 12:02
If you need to search inside arrays, chances are that you want a properly normalized data model to begin with. — user330315
– user330315, Commented Nov 15, 2021 at 12:04
json with an array means json field, but json[] means array of jsons - not really the same thing. And for OP question, json[] seems to be a better choice. — Greg
– Greg, Commented Nov 15, 2021 at 12:05
so whats the advantage with your approach then? Because at least merging 2 arrays is obviously necessary — user2741831
– user2741831, Commented Nov 15, 2021 at 12:06

Greg · Accepted Answer · 2021-11-15 12:21:05Z

1

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.

answered Nov 15, 2021 at 12:21

Greg

6,7911 gold badge29 silver badges61 bronze badges

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

2 Comments

user2741831 Over a year ago

thanks. I since found a third method and added it to my post

Greg Over a year ago

Nice, I'd suggest adding it as an answer as well.

user2741831 · Accepted Answer · 2021-11-16 10:08:19Z

1

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 *

EXCLUDED explanation

https://www.postgresql.org/docs/9.5/sql-insert.html

edited Nov 16, 2021 at 10:08

answered Nov 15, 2021 at 18:06

user2741831

2,4823 gold badges30 silver badges61 bronze badges

2 Comments

Greg Over a year ago

Can you explain where this EXCLUDED comes from?

user2741831 Over a year ago

The excluded row is the row you wanted to insert. meaning you add to the existing place.headlines the headlines you just inserted

Collectives™ on Stack Overflow

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

2 Answers 2

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related