Retain only 3 highest positive and negative records in a table

Question

I am new to databases and postgres as such. I have a table called names which has 2 columns name and value which gets updated every x seconds with new name value pairs. My requirement is to retain only 3 positive and 3 negative values at any point of time and delete the rest of the rows during each table update. I use the following query to delete the old rows and retain the 3 positive and 3 negative values ordered by value.

delete from names
using (select  *,
row_number() over (partition by value > 0, value < 0 order by value desc) as rn
from names ) w
where w.rn >=3

I am skeptical to use a conditional like value > 0 in a partition statement. Is this approach correct?

For example,

A table like this prior to delete :

name  | value 
--------------
test  |  10 
test1 |  11
test1 |  12
test1 |  13
test4 |  -1
test4 |  -2

My table after delete should look like :

name  | value 
--------------
test1 |  13
test1 |  12
test1 |  11
test4 |  -1 
test4 |  -2

Answer 1

demo:db<>fiddle

This works generally as expected: value > 0 clusters the values into all numbers > 0 and all numbers <= 0. The ORDER BY value orders these two groups as expected well.

So, the only thing, I would change:

row_number() over (partition by value >= 0 order by value desc) 

1. remove: , value < 0 (Because: Why should you group the positive values into negative and other? You don't have any negative numbers in your positive group and vice versa.)
2. change: value > 0 to value >= 0 to ignore the 0 as long as possible

---

For deleting: If you want to keep the top 3 values of each direction:

1. you should change w.rn >= 3 into w.rn > 3 (it keeps the 3rd element as well)
2. you need to connect the subquery with the table records. In real cases you should use id columns for that. In your example you could take the value column: where n.value = w.value AND w.rn > 3

So, finally:

delete from names n
using (select  *,
row_number() over (partition by value >= 0 order by value desc) as rn
from names ) w
where n.value = w.value AND w.rn > 3