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

Question

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?

Please define *first* first. (the records with the lowest timestamps?) — joop, May 22 '15 at 09:19
possible duplicate of [How do I delete a fixed number of rows with sorting in PostgreSQL?](http://stackoverflow.com/questions/5170546/how-do-i-delete-a-fixed-number-of-rows-with-sorting-in-postgresql) — chresse, May 22 '15 at 09:19

score 16 · Accepted Answer · edited May 23 '15 at 04:25

16

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)

edited May 23 '15 at 04:25

Vivek S.

19,945
7
68
85

answered May 22 '15 at 09:18

Robert

25,425
8
67
81

1

unfortunately after i apply this command in production then i lost all my records :(. why did u do that? – aligur Oct 23 '20 at 20:06
3

You should never do something like that in production without testing – kasvith Jan 07 '21 at 17:19

Vivek S. · Answer 2 · 2016-08-07T11:00:47.023

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

As per Documentation

ctid
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 each time 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.

score 1 · Answer 3 · answered May 22 '15 at 09:29

1

Here how to do it without 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
  )

answered May 22 '15 at 09:29

Houari

5,326
3
31
54

score 1 · Answer 4 · edited Sep 21 '22 at 08:29

Ctid answer (2nd most popular) is the best imo (most bullet-proof). The most upvoted approach can give lot of troubles as it did to me (especially in PK-less tables).

If someone prefers more imperative way PL/pgSQL block structure with LOOP can be used like this. Expect lower performance than single query, though.

do $$        
declare rows_to_delete integer := 2;
begin
   for i in 1..rows_to_delete loop
        delete from txn_log where ctid in (select min(CTID) from txn_log);
        raise notice 'Iteration % finished',i;
   end loop;
end; $$

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

4 Answers4

Related