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

Question

I want to remove the first 500 records from my table without any conditions.

The table definition is as follows:

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

I do not have a primary key. I tried to remove using

DELETE FROM txn_log LIMIT 500

but he throws an error:

ERROR: syntax error in or near "LIMIT": REMOVE FROM TXN_LOG LIMIT 5000 ^

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

ERROR: syntax error at or near "LIMIT"

Can someone suggest me a way to do this?

Answer 1

Try preparing a subquery with LIMITas 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)

Answer 2

DELETE
FROM txn_log
WHERE ctid IN (
        SELECT ctid
        FROM txn_log
        ORDER BY txn_log_timestamp limit 500
        )

By Documentation

CTID
  . , ctid , ctid , . ctid . OID . .

Answer 3

Here's how to do it without a condition:

DELETE FROM txn_log
WHERE (txn_log_pgm_id,txn_log_timestamp)  IN 
  (
  SELECT txn_log_pgm_id,txn_log_timestamp
  FROM txn_log
  LIMIT 500
  )