Geek asks and answers

Delete old temporary login log lines from postgresql table

I have a postgresql table (v8.4) with login ID and timestamps, and I want to create a batch file that will be run periodically to delete records when the same identifier contains more than X number of records in the table. In addition, the number of stored records is stored in a separate table and may be different for each identifier.

The corresponding columns of the two tables are:

CREATE TABLE user_profile (
  id varchar(256) PRIMARY KEY,
  history_count integer
)

CREATE TABLE access_history (
  id varchar(256),
  login_time timestamp
)

The solution I am considering includes 3 SQL commands:

  • get all login IDs with excess entries

    SELECT access_history.id, count(*), user_profile.history_count
  FROM d_access_history 
  LEFT JOIN d_user_profile 
  ON access_history.id = user_profile.id 
  GROUP BY access_history.id, user_profile.history_count
  HAVING count(*) > user_profile.history_count;

  • swipe through each record from the above query and get a timestamp for the last record

    SELECT login_time 
  FROM access_history 
  WHERE id = 'user id'
  ORDER BY login_time DESC 
  OFFSET 200 LIMIT 1;

  • delete records older than timestamp obtained from # 2

    DELETE from access_history 
  WHERE id = 'user id'
  AND login_time <= '2011-06-06 10:22:29.604156'

, , SQL, , .

+3
1
with cte
as
(
    select id, row_number() over (order by login_time desc) RowNumber
    from access_history
)
delete cte
where RowNumber > 200

200 , .

[EDIT]

a_horse_with_no_name, cte with delete 8.4.
delete , .

delete access_history
where id in
(
    select id
    from
    (
        select id, row_number() over (order by login_time desc) RowNumber
        from access_history
    ) tt
    where RowNumber > 200
)
+2

More articles:


All Articles