PostgreSQL: How to Delete First N Rows in Table

Often in PostgreSQL you may want to delete the first N rows in a table.

You can use the following syntax to do so:

DELETE FROM athletes
WHERE ctid IN (
    SELECT ctid
    FROM athletes
    ORDER BY points
    LIMIT 5
);

This particular example will delete the first 5 rows from the athletes table, after the table has been sorted in ascending order by the values in the points column.

NOTE: The ctid is a unique column for each table in PostgreSQL that is able to uniquely identify each row.

The following example shows how to use this syntax in practice.

Example: How to Delete First N Rows in PostgreSQL

Suppose that we create the following table named athletes that contains information about various basketball players:

-- create table named 'athletes'
CREATE TABLE athletes (
  id INTEGER PRIMARY KEY,
  team TEXT,
  points INTEGER
);

-- add rows to the table 
INSERT INTO athletes VALUES (0001, 'Mavs', 22);
INSERT INTO athletes VALUES (0002, 'Mavs', 30);
INSERT INTO athletes VALUES (0003, 'Mavs', 12);
INSERT INTO athletes VALUES (0004, 'Mavs', 9);
INSERT INTO athletes VALUES (0005, 'Mavs', 14);
INSERT INTO athletes VALUES (0006, 'Heat', 16);
INSERT INTO athletes VALUES (0007, 'Heat', 40);
INSERT INTO athletes VALUES (0008, 'Heat', 18);
INSERT INTO athletes VALUES (0009, 'Heat', 46);
INSERT INTO athletes VALUES (0010, 'Heat', 33);

-- view all rows in table
SELECT * FROM athletes;

 id | team | points 
----+------+--------
  1 | Mavs |     22
  2 | Mavs |     30
  3 | Mavs |     12
  4 | Mavs |      9
  5 | Mavs |     14
  6 | Heat |     16
  7 | Heat |     40
  8 | Heat |     18
  9 | Heat |     46
 10 | Heat |     33

Suppose that we would like to delete the first 5 rows from the table, after the table has been sorted in ascending order by the values in the points column.

We can use the following syntax to do so:

-- delete 5 rows with lowest values in points column
DELETE FROM athletes
WHERE ctid IN (
    SELECT ctid
    FROM athletes
    ORDER BY points
    LIMIT 5
);

-- view updated table
SELECT * FROM athletes;

 id | team | points 
----+------+--------
  1 | Mavs |     22
  2 | Mavs |     30
  7 | Heat |     40
  9 | Heat |     46
 10 | Heat |     33

We can see that the five rows with the lowest values in the points column have been deleted from the table.

Feel free to use similar syntax to delete the first N rows from your own table in PostgreSQL.

Note: To delete a different number of rows, simply change LIMIT 5 to a different number, e.g. to delete the first 10 rows you can use LIMIT 10 instead.

Additional Resources

The following tutorials explain how to perform other common tasks in PostgreSQL:

PostgreSQL: How to Delete Empty Rows in Table
PostgreSQL: How to Delete All Rows Older than N Days
PostgreSQL: How to Replace Null Values with String