1

Let's say I have the following table, with data:

Id User Item Time
1 1 Banana D+1
2 1 Banana D+2
3 1 Banana D+3
4 2 Apple D+1
5 1 Apple D+2
6 2 Apple D+3
7 2 Apple D+4

The column definitions are: UUID (pk), UUID, TEXT, TIMESTAMPTZ respectively. None of the columns allow nulls.

New rows are added frequently, so I've decided I need a cleanup job to run on a schedule. (Done via my application code)

In order to not keep too much data, I've decided I want to keep the latest 2 entries, per user and item. I guess there could be, in rare cases, time duplicates. If so, any one of the two items should be removed.

My intended result should look like:

Id User Item Time
2 1 Banana D+2
3 1 Banana D+3
5 1 Apple D+2
6 2 Apple D+3
7 2 Apple D+4

I'm not sure how to accomplish this delete query.

0

2 Answers 2

3

Sample data:

create table user_items (
    user_items_id serial primary key,
    user_id uuid,
    item_name text,
    purchase_time timestamptz
);

insert into user_items (user_id, item_name, purchase_time)
values ('7f89114c-04df-4a80-afe1-f79e819470de', 'Banana', '2022-01-01'),
    ('7f89114c-04df-4a80-afe1-f79e819470de', 'Banana', '2022-01-02'),
    ('7f89114c-04df-4a80-afe1-f79e819470de', 'Banana', '2022-01-03'),
    ('8d0f7f91-2e3b-49e9-b81f-0e800f9a967b', 'Apple', '2022-01-01'),
    ('7f89114c-04df-4a80-afe1-f79e819470de', 'Apple', '2022-01-02'),
    ('8d0f7f91-2e3b-49e9-b81f-0e800f9a967b', 'Apple', '2022-01-03'),
    ('8d0f7f91-2e3b-49e9-b81f-0e800f9a967b', 'Apple', '2022-01-04');

Since some users may only have 1 item and some of them may need to be kept (since even if it's just 1 it's still part of the "last 2 items"), I have edited my solution to work in a different manner.

The following query will delete the corresponding rows and leaving only the last 2 (or less) entries partitioned by user and item. This will also delete one of 2 duplicates should they exist.

delete from user_items
where user_items_id in (
    select x.user_items_id
    from (
        select
            row_number() over(partition by ui.user_id, ui.item_name order by ui.purchase_time desc) as rn,
            ui.user_items_id
        from user_items ui
    ) x
    where x.rn > 2
);

Running a select statement on the table after running this delete will show the following:

user_items_id user_id item_name purchase_time
2 7f89114c-04df-4a80-afe1-f79e819470de Banana 2022-01-02 00:00:00.000000 +00:00
3 7f89114c-04df-4a80-afe1-f79e819470de Banana 2022-01-03 00:00:00.000000 +00:00
5 7f89114c-04df-4a80-afe1-f79e819470de Apple 2022-01-02 00:00:00.000000 +00:00
6 8d0f7f91-2e3b-49e9-b81f-0e800f9a967b Apple 2022-01-03 00:00:00.000000 +00:00
7 8d0f7f91-2e3b-49e9-b81f-0e800f9a967b Apple 2022-01-04 00:00:00.000000 +00:00

You may change the filter to x.rn > 1 to delete everything except the last entry (so keeping only 1 entry instead of 2)

You may also use this solution to achieve the same result of keeping the last entry only:

delete from user_items
where user_items_id not in (
    select distinct on (f.user_id, item_name)
        f.user_items_id
    from user_items f
    order by user_id, item_name, purchase_time desc
);

Which would return the following after deletion:

user_items_id user_id item_name purchase_time
3 7f89114c-04df-4a80-afe1-f79e819470de Banana 2022-01-03 00:00:00.000000 +00:00
5 7f89114c-04df-4a80-afe1-f79e819470de Apple 2022-01-02 00:00:00.000000 +00:00
7 8d0f7f91-2e3b-49e9-b81f-0e800f9a967b Apple 2022-01-04 00:00:00.000000 +00:00
0
2

Copy the rows to be retained to a working table. Truncate the original table. Copy back the retained rows.

This may be faster if a relatively small fraction of existing rows are retained as truncation does not require subsequent vacuuming.

1
  • Very interesting solution, never thought about that Commented Jul 8, 2022 at 12:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.