Menu
First, specify the table from which you want to delete data in the DELETE FROM clause. Second, specify which rows to delete by using the condition in the WHERE clause. The WHERE clause is optional. However, if you omit it, the DELETE statement will delete all rows in the table.
The PostgreSQL LIMIT clause is used to get a subset of rows generated by a query. It is an optional clause of the SELECT statement. The LIMIT clause can be used with the OFFSET clause to skip a specific number of rows before returning the query for the LIMIT clause.
SELECT a, b FROM table1 ORDER BY a + b, c; When more than one expression is specified, the later values are used to sort rows that are equal according to the earlier values. Each expression can be followed by an optional ASC or DESC keyword to set the sort direction to ascending or descending. ASC order is the default.
Right-click in a table cell, row, or column you want to delete. On the menu, click Delete Cells. To delete one cell, choose Shift cells left or Shift cells up. To delete the row, click Delete entire row.
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
ctidcan be used to locate the row version very quickly, a row'sctidwill change if it is updated or moved byVACUUM FULL. Thereforectidis 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.
Postgres docs recommend to use array instead of IN and subquery. This should work much faster
DELETE FROM logtable
WHERE id = any (array(SELECT id FROM logtable ORDER BY timestamp LIMIT 10));
This and some other tricks can be found here
delete from logtable where log_id in (
select log_id from logtable order by timestamp limit 10);
Assuming you want to delete ANY 10 records (without the ordering) you could do this:
DELETE FROM logtable as t1 WHERE t1.ctid < (select t2.ctid from logtable as t2 where (Select count(*) from logtable t3 where t3.ctid < t2.ctid ) = 10 LIMIT 1);
For my use case, deleting 10M records, this turned out to be faster.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With