I'm curious if Postgres has anything in place where I could limit the amount of rows kept for a ID.
For example, say I have a users table and login_coordinates table. Every time the user logs in, the most current coordinates get fed to a column in the users table, as well as inserting into the login_coordinates table.
I only want to keep the last 10 records, and deleting the 11th(oldest) record in the login_coordinates table for all users.
Users
user_id | current_coordinates |
----------------------+----------------------------+
1 | aaaa.bbbbb, aaaaa.bbbbbb |
2 | zzzz.xxxxxx, xxxxx.xxxcxx |
3 | dddd.xxxxxx, xxxxx.xxxcxx |
Login Coordinates
coordinates_id | old_login_coordinates | user_id |
----------------------+----------------------------+--------------------------+
1 | aaaa.bbbbb, aaaaa.bbbbbb | 1 |
2 | xxxxx.xxxxxx, xxxxx.xxxcxx | 1 |
3 | xxxxx.xxxxxx, xxxxx.xxxcxx | 1 |
Is there anything that would limit the records to 10 coordinate_id per user, always deleting the oldest records?
I'm using PostgreSQL 9.5.