PostgreSQL – Update Limit

database-designpostgresqlpostgresql-9.5

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.

Best Answer

as well as inserting into the login_coordinates table

I don't see the benefit of redundant storage. Just write to the table login_coordinates. Easy enough to access with only 10 rows per user. Don't update the user row as well.

Basically, what @Ziggy already suggested, with more flesh. Based on this table:

CREATE TABLE login_coordinates (
  login_coordinates_id serial PRIMARY KEY
, user_id integer NOT NULL  -- REFERENCES users
, login_at timestamptz NOT NULL DEFAULT now()
, coordinates point NOT NULL
);

Using OFFSET 9 LIMIT 1 to pick nr. 10:

DELETE FROM login_coordinates lc
WHERE  login_at <= (
   SELECT login_at
   FROM   login_coordinates
   WHERE  user_id = lc.user_id
   ORDER  BY login_at DESC
   OFFSET 9  -- only found if at least 10 rows present
   LIMIT  1
   )
AND    user_id = 1;

INSERT INTO login_coordinates (user_id, coordinates)
VALUES (1, '(1, 2)');
Related Question