Rushikesh Rushikesh - 3 months ago 10
SQL Question

How to delete first few records from a table without any criteria in PostgreSQL?

I want to delete first 500 records from my table without any condition.

Table definition is as follow:

CREATE TABLE txn_log
(
txn_log_timestamp timestamp without time zone NOT NULL,
txn_log_pgm_id character(6)
)
WITH (OIDS=FALSE);


I don't have any primary key here. I tried to delete using

DELETE FROM txn_log LIMIT 500


but it throwing error:


ERROR: syntax error at or near "LIMIT" LINE 1: DELETE FROM TXN_LOG
LIMIT 5000
^

********** Error **********

ERROR: syntax error at or near "LIMIT"


Can someone suggest me a way to do this?

Answer

Try to prepare subquery with LIMIT as below

DELETE FROM txn_log
WHERE txn_log_pgm_id IN (SELECT txn_log_pgm_id
                         FROM txn_log
                         ORDER BY txn_log_timestamp asc
                         LIMIT 500)
Comments