how to delete all rows after 1000 rows in postgresql Table-CodePudding

I have huge Database with 15 tables. I need to make light version of that and leave only first 1000 rows in each table based on DESC Date. I did try to find on google how to do that but nothing really works.

It will be perfect it there will be automated way to go through each table and leave only 1000 rows. But If I need to do that manually with each table it will be fine as well.

Thank you,

CodePudding user response：

This looks positively awful, but maybe it's a starting point from which you can build.

with cte as (
  select mod_date, row_number() over (order by mod_date desc) as rn
  from table1
),
min_date as (
  select mod_date
  from cte
  where rn = 1000
)
delete from table1 t1
where t1.mod_date < (select mod_date from min_date)

CodePudding user response：

So solution is:

DELETE FROM "table" WHERE "date" < now() - interval '1 year';

That way it will delete all data from table where Date is older that 1 year.