How to set “expiry” time after data entry is automatically deleted?

How to set “expiry” time after data entry is automatically deleted?

Asked on October 27, 2018 in Database.
Add Comment


  • 5 Answer(s)

    Below is an example of a trigger that deletes rows from a table that have a timestamp of older than 1 minute.
    It is executed whenever a new row is inserted into that same table.
    Then can obviously set the trigger to execute on other conditions and for various expiration dates as need be.

    CREATE TABLE expire_table (
        timestamp timestamp NOT NULL DEFAULT NOW(),
        name TEXT NOT NULL
    );
    INSERT INTO expire_table (name) VALUES ('a');
    INSERT INTO expire_table (name) VALUES ('b');
    INSERT INTO expire_table (name) VALUES ('c');
     
    select * from expire_table;
        timestamp              | name
    ----------------------------+------
    2014-09-26 15:33:43.243356 | a
    2014-09-26 15:33:45.222202 | b
    2014-09-26 15:33:47.347131 | c
    (3 rows)
    CREATE FUNCTION expire_table_delete_old_rows() RETURNS trigger
        LANGUAGE plpgsql
        AS $$
    BEGIN
        DELETE FROM expire_table WHERE timestamp < NOW() - INTERVAL '1 minute';
        RETURN NEW;
    END;
    $$;
    CREATE TRIGGER expire_table_delete_old_rows_trigger
        AFTER INSERT ON expire_table
        EXECUTE PROCEDURE expire_table_delete_old_rows();
     
    INSERT INTO expire_table (name) VALUES ('d');
     
    select * from expire_table;
    timestamp                  | name
    ----------------------------+------
    2014-09-26 15:36:56.132596 | d
    (1 row)
    the code can be used to we set “expiry” time, to a data entry even after it is automatically deleted in PostgreSQL
    
    Answered on October 27, 2018.
    Add Comment

    As per we known there no such feature.

    we could only these options
    one is the “expired” timestamp other is the
    timestamp + cron -job/pg Agent.

    Answered on October 27, 2018.
    Add Comment

    There is no built in expiration feature but if your goal is to automatically expire fields and have the logic contained within your database (and thus no outside dependency like a cron job) then you can always write a trigger. Below is an example of a trigger that deletes rows from a table that have a timestamp of older than 1 minute. It is executed whenever a new row is inserted into that same table. You can obviously set the trigger to execute on other conditions and for various expiration dates as need be.

    CREATE TABLE expire_table (
        timestamp timestamp NOT NULL DEFAULT NOW(),
        name TEXT NOT NULL
    );
    
    INSERT INTO expire_table (name) VALUES ('a');
    INSERT INTO expire_table (name) VALUES ('b');
    INSERT INTO expire_table (name) VALUES ('c');
    
    select * from expire_table;
             timestamp          | name 
    ----------------------------+------
     2014-09-26 15:33:43.243356 | a
     2014-09-26 15:33:45.222202 | b
     2014-09-26 15:33:47.347131 | c
    (3 rows)
    
    CREATE FUNCTION expire_table_delete_old_rows() RETURNS trigger
        LANGUAGE plpgsql
        AS $$
    BEGIN
      DELETE FROM expire_table WHERE timestamp < NOW() - INTERVAL '1 minute';
      RETURN NEW;
    END;
    $$;
    
    CREATE TRIGGER expire_table_delete_old_rows_trigger
        AFTER INSERT ON expire_table
        EXECUTE PROCEDURE expire_table_delete_old_rows();
    
    INSERT INTO expire_table (name) VALUES ('d');
    
    select * from expire_table;
             timestamp          | name 
    ----------------------------+------
     2014-09-26 15:36:56.132596 | d
    (1 row)
    Answered on January 14, 2019.
    Add Comment

    Is there any way to set some sort of “expiry” time on data entries in PostgreSQL? I’m thinking about something equivalent to EXPIRE in Redis.

    I’m not looking to store a timestamp and then manually code some sort of cron job to check what entries have expired.

    I’m trying to find out if there’s any native feature in PostgreSQL that would provide this kind of functionality, or if it would make sense to request such feature for future releases

    Answered on January 14, 2019.
    Add Comment

    is there any way to set some sort of “expiry” time on data entries in PostgreSQL? I’m thinking about something equivalent to EXPIRE in Redis.

    I’m not looking to store a timestamp and then manually code some sort of cron job to check what entries have expired.

    I’m trying to find out if there’s any native feature in PostgreSQL that would provide this kind of functionality, or if it would make sense to request such feature for future releases.

    Answered on February 20, 2019.
    Add Comment


  • Your Answer

    By posting your answer, you agree to the privacy policy and terms of service.