You can detect is this row first or last for uid by ranging with "row_number" and counting rows for uid.
First row have rn=1. Last row have rn=uidCnt.
If only 1 row exists for uid, then rn=1, uidCnt=1 - this is first and last row.
It is possible that there are several rows in the table for the uid and date. For the sake of certainty, we include the Row Id in the ORDER BY.
In example rows - "candidates to delete" tagged with flag 'D'.
with rangedData as(
select *
,row_number()over(partition by uid order by "when" asc, id asc)rn
,count(*)over(partition by uid) uidCnt
from calls2
)
select *
,case when rn=1 or rn=uidCnt then 'A' else 'D' end fl
from rangedData
| id |
uid |
when |
other |
rn |
uidcnt |
fl |
| 1 |
11 |
2010-01-01 |
a1 |
1 |
4 |
A |
| 2 |
11 |
2010-01-02 |
a2 |
2 |
4 |
D |
| 3 |
11 |
2010-01-03 |
a3 |
3 |
4 |
D |
| 4 |
11 |
2010-01-04 |
a4 |
4 |
4 |
A |
| 5 |
22 |
2010-01-01 |
b1 |
1 |
1 |
A |
| 6 |
33 |
2010-01-01 |
c1 |
1 |
2 |
A |
| 7 |
33 |
2010-01-02 |
c2 |
2 |
2 |
A |
| 8 |
44 |
2010-01-01 |
d1 |
1 |
3 |
A |
| 9 |
44 |
2010-01-02 |
d2 |
2 |
3 |
D |
| 10 |
44 |
2010-01-03 |
d3 |
3 |
3 |
A |
Rows to delete
with rangedData as(
select *
,row_number()over(partition by uid order by "when" asc, id asc)rn
,count(*)over(partition by uid) uidCnt
from calls2
)
select *
,case when rn=1 or rn=uidCnt then 'A' else 'D' end fl
from rangedData
where rn>1 and rn<uidCnt
order by uid,"when",id
| id |
uid |
when |
other |
rn |
uidcnt |
fl |
| 2 |
11 |
2010-01-02 |
a2 |
2 |
4 |
D |
| 3 |
11 |
2010-01-03 |
a3 |
3 |
4 |
D |
| 9 |
44 |
2010-01-02 |
d2 |
2 |
3 |
D |
Actual rows
with rangedData as(
select *
,row_number()over(partition by uid order by "when" asc, id asc)rn
,count(*)over(partition by uid) uidCnt
from calls2
)
select *
,case when rn=1 or rn=uidCnt then 'A' else 'D' end fl
from rangedData
where rn=1 or rn=uidCnt
order by uid,"when",id
| id |
uid |
when |
other |
rn |
uidcnt |
fl |
| 1 |
11 |
2010-01-01 |
a1 |
1 |
4 |
A |
| 4 |
11 |
2010-01-04 |
a4 |
4 |
4 |
A |
| 5 |
22 |
2010-01-01 |
b1 |
1 |
1 |
A |
| 6 |
33 |
2010-01-01 |
c1 |
1 |
2 |
A |
| 7 |
33 |
2010-01-02 |
c2 |
2 |
2 |
A |
| 8 |
44 |
2010-01-01 |
d1 |
1 |
3 |
A |
| 10 |
44 |
2010-01-03 |
d3 |
3 |
3 |
A |
DELETE action
delete from calls2 dest
using (
select *
,row_number()over(partition by uid order by "when" asc, id asc)rn
,count(*)over(partition by uid) uidCnt
from calls2
) rangedData
where rangedData.id=dest.id
and (rangedData.rn<>1 and rangedData.rn<uidCnt)
returning *
| id |
uid |
when |
other |
id |
uid |
when |
other |
rn |
uidcnt |
| 2 |
11 |
2010-01-02 |
a2 |
2 |
11 |
2010-01-02 |
a2 |
2 |
4 |
| 3 |
11 |
2010-01-03 |
a3 |
3 |
11 |
2010-01-03 |
a3 |
3 |
4 |
| 9 |
44 |
2010-01-02 |
d2 |
9 |
44 |
2010-01-02 |
d2 |
2 |
3 |
If the table is quite large, it is better to replace "SELECT * .." in the query with "SELECT id, uid, when" and add an index like "CREATE index idx_calls2_uid,when_id on calls2(uid,when,id)". I assume that you already have such an index.
Then the "table scan" operation may be replaced by a faster "index scan" operation. Also some sorting's inside the query will be removed.
fiddle
Update1
See this part of query plan
Delete on public.calls2 dest (cost=142.62..241.61 rows=2119 width=150) (actual time=0.138..0.147 rows=3 loops=1)
Output: dest.id, dest.uid, dest."when", dest.other, rangeddata.id, rangeddata.uid, rangeddata."when", rangeddata.other, rangeddata.rn, rangeddata.uidcnt
-> Hash Join (cost=142.62..241.61 rows=2119 width=150) (actual time=0.171..0.179 rows=3 loops=1)
Output: dest.ctid, rangeddata.*, rangeddata.id, rangeddata.uid, rangeddata."when", rangeddata.other, rangeddata.rn, rangeddata.uidcnt
Hash Cond: (dest.id = rangeddata.id)
-> Seq Scan on public.calls2 dest (cost=0.00..21.30 rows=1130 width=10) (actual time=0.005..0.007 rows=10 loops=1)
Output: dest.ctid, dest.id
-> Hash (cost=137.93..137.93 rows=375 width=144) (actual time=0.111..0.112 rows=3 loops=1)
We can see "Hash JOIN" with 2 sources: target table "public.calls2 dest" and subquery "rangeddata.*" with condition "(dest.id = rangeddata.id)".
We can assume that the DBMS first executes such a query and deletes the rows from the main table (DELETE FROM ...) that are included in the query result. This is the usual way to execute the DELETE command when using two sources.
select *
from calls2 dest
,(
select *
,row_number()over(partition by uid order by "when" asc, id asc)rn
,count(*)over(partition by uid) uidCnt
from calls2
) rangedData
where rangedData.id=dest.id
and (rangedData.rn<>1 and rangedData.rn<uidCnt)
| id |
uid |
when |
other |
id |
uid |
when |
other |
rn |
uidcnt |
| 2 |
11 |
2010-01-02 |
a2 |
2 |
11 |
2010-01-02 |
a2 |
2 |
4 |
| 3 |
11 |
2010-01-03 |
a3 |
3 |
11 |
2010-01-03 |
a3 |
3 |
4 |
| 9 |
44 |
2010-01-02 |
d2 |
9 |
44 |
2010-01-02 |
d2 |
2 |
3 |
Full query plan see in fiddle
Update2
Your question in the comment prompted a desire to formulate a solution in a different way. Here is an example of a query that fulfills the conditions you have formulated verbatim.
select *
-- delete
from calls2 dest
where
exists( -- row before
select 1 from calls2 t1
where t1.uid=dest.uid and t1."when"<=dest."when" and t1.id<dest.id)
and exists( -- row after
select 1 from calls2 t1
where t1.uid=dest.uid and t1."when">=dest."when" and t1.id>dest.id)
(There "select 1" is "select anything")
Fiddle