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

by

You could try using the ctid:

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

The ctid is:

The physical location 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.

There’s also oid but that only exists if you specifically ask for it when you create the table.

More Related Contents:

  1. Sort by column ASC, but NULL values first?
  2. Using an Alias column in the where clause in Postgresql
  3. How to return result of a SELECT inside a function in PostgreSQL?
  4. Grouped LIMIT in PostgreSQL: show the first N rows for each group?
  5. Reset auto increment counter in postgres
  6. Optimize query with OFFSET on large table
  7. How to concatenate columns in a Postgres SELECT?
  8. How to use (install) dblink in PostgreSQL?
  9. get JOIN table as array of results with PostgreSQL/NodeJS
  10. updating table rows in postgres using subquery
  11. How can I send email from PostgreSQL trigger?
  12. Find overlapping date ranges in PostgreSQL
  13. PostgreSQL multi INSERT…RETURNING with multiple columns
  14. How do you do date math that ignores the year?
  15. “Operator does not exist: integer =?” when using Postgres
  16. Extract date (yyyy/mm/dd) from a timestamp in PostgreSQL
  17. Oracle equivalent of Postgres’ DISTINCT ON?
  18. How much disk-space is needed to store a NULL value using postgresql DB?
  19. Aggregate a single column in query with many columns
  20. Aggregating (x,y) coordinate point clouds in PostgreSQL
  21. PostgreSQL convert columns to rows? Transpose?
  22. Does Postgres support nested or autonomous transactions?
  23. Regular expression in PostgreSQL LIKE clause
  24. Why is iterating through a large Django QuerySet consuming massive amounts of memory?
  25. Postgres Error: More than one row returned by a subquery used as an expression
  26. Check if NULL exists in Postgres array
  27. Generate DEFAULT values in a CTE UPSERT using PostgreSQL 9.3
  28. Postgresql query between date ranges
  29. How to use SQL LIKE condition with multiple values in PostgreSQL?
  30. Why isn’t row level security enabled for Postgres views?

Leave a Comment