How do I delete a fixed number of rows with sorting in PostgreSQL ?

How do I delete a fixed number of rows with sorting in PostgreSQL ?

Asked on January 9, 2019 in PostgreSQL.
Add Comment


  • 3 Answer(s)

    User refer this website  ctid

    DELETE FROM logtable
    WHERE ctid IN (
        SELECT ctid
        FROM logtable
        ORDER BY timestamp
        LIMIT 10
    )
    

    The environmental district of the row version within its table. Note that although the ctid can be used to locate the row version very quickly, a row’s ctid will change if it is updated or moved by VACUUM FULL. Therefore ctid is useless as a long-term row identifier.

    Answered on January 9, 2019.
    Add Comment

    postgresql document advocate to use array instead of IN and subquery. This should do much speed.

    DELETE FROM logtable
    WHERE id = any (array(SELECT id FROM logtable ORDER BY timestamp LIMIT 10));
    

    You open this Link some tricks here

    Answered on January 9, 2019.
    Add Comment

    Below example command

    delete from logtable where log_id in (
    select log_id from logtable order by timestamp limit 10);
    
    Answered on January 9, 2019.
    Add Comment


  • Your Answer

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