how delete row with PostgreSQL RLS - supabase-CodePudding

i tried to create policy for delete rows older then '10 seconds'

CREATE POLICY "DELETE" ON "public"."pool"
AS PERMISSIVE FOR DELETE
TO public
USING ((created_at < (now() - interval '10 seconds')))

but supabase replace with (created_at < (now() - '00:00:10'::interval))

is there any way to delete rows after a while on supabase?

CodePudding user response：

You cannot use a policy to delete something in your database. You will need to create a cron job to do this instead. You will first need to enable the extension and then you can use it like:

select
  cron.schedule(
    'cron-name', -- name of the cron job
    '* * * * *', -- every minute
    $$
     -- Put your code between two dollar signs so that you can create full statements.
     -- Alternatively, you can write you code in a Postgres Function and call it here.
     -- Your delete query would go here
    $$
  );

You can read more about this on the blog https://supabase.com/blog/postgres-as-a-cron-server