Delete the Certain number of Rows from Postgresql table

Question

I am new to postgresql, I have inserted 5000 rows in my table but I just want to delete the 1500 rows from 5000 rows. Here I don't have any constraint to delete those rows. I have to delete the top or bottom 1500 rows from the table.

I have googled a lot but I have not get any clue to delete the rows without any constraint.

Any suggestion would be great.

There is no internal order in a Postgres database. You should delete 1500 records with regard to some column order. — Tim Biegeleisen
– Tim Biegeleisen, Commented Jun 9, 2016 at 10:29
There is no such thing as the "bottom 1500 rows" in a relational database. — user330315
– user330315, Commented Jun 9, 2016 at 11:05

khelili miliana · Accepted Answer · 2016-06-09 10:22:45Z

6

DELETE FROM YourTable
WHERE ctid IN (
SELECT ctid
FROM YourTable
ORDER BY timestamp
LIMIT 1500
)

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. The OID, or even better a user-defined serial number, should be used to identify logical rows.

answered Jun 9, 2016 at 10:22

khelili miliana

3,8222 gold badges18 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Ezequiel Tolnay Over a year ago

Note that ctid IN (SELECT ctid FROM ... LIMIT n) is awfully slow. The following runs way faster: ctid = ANY(ARRAY(SELECT ctid FROM ... LIMIT n))

Collectives™ on Stack Overflow

Delete the Certain number of Rows from Postgresql table

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related