How to delete unnecessary records from postgresql database table

Question

I have a table that includes versions of my table.

version_id     transaction    entity_id    date
--------------------------------------------------------------
1              insert         1            11.11.2019 13:15:04
2              update         1            11.11.2019 13:16:04
3              insert         8            11.11.2019 13:17:04
4              update         1            11.11.2019 13:18:04
5              update         8            11.11.2019 13:19:04
6              update         1            11.11.2019 13:20:04
7              update         1            11.11.2019 13:21:04
8              delete         1            11.11.2019 13:22:04

For example entity id 1, I need 1,7,8 version numbers. 2,4,6 version numbers are not needed, so I can delete them. Actually I need last insert, update, delete transaction version numbers for an entity. The others will delete.

Can I remove all unnecessary update ids in this way?

Gordon Linoff · Accepted Answer · 2019-12-11 11:42:20Z

1

I need last insert, update, delete transaction version numbers for an entity.

You can delete the rows using:

delete from version v
    where v.version_id < (select max(v2.version_id)
                          from versions v2
                          where v2.entity_id = v.entity_id and
                                v2.transaction = v.transaction
                         );

answered Dec 11, 2019 at 11:42

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

Comments

Nick · Accepted Answer · 2019-12-11 12:07:39Z

If "last" is defined by the value of date, you can use this query to delete earlier versions:

DELETE FROM versions v1
WHERE date < (SELECT MAX(date)
              FROM versions v2
              WHERE v2.entity_id = v1.entity_id
                AND v2.transaction = v1.transaction)

Output (from SELECT * after DELETE) for your sample data:

version_id  transaction     entity_id   date
1           insert          1           2019-11-11T13:15:04.000Z
3           insert          8           2019-11-11T13:17:04.000Z
5           update          8           2019-11-11T13:19:04.000Z
7           update          1           2019-11-11T13:21:04.000Z
8           delete          1           2019-11-11T13:22:04.000Z

Demo on dbfiddle

Collectives™ on Stack Overflow

How to delete unnecessary records from postgresql database table

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related