Delete with sorting in PostgreSQL

Question

I am trying to delete rows in a specific order. I tried the suggestions in the post How do I delete a fixed number of rows with sorting in PostgreSQL?, but nothing worked. I know they did not work because I had a trigger on delete and seeing which one is deleted and it is never doing it in the order that I want it to.

The tests that I wrote are like this:

create table test1 (
    th text,
    co text,
    ty text);

CREATE OR REPLACE FUNCTION testfunc() RETURNS TRIGGER
LANGUAGE plpgsql
AS $$

DECLARE modified_row test1%ROWTYPE;
BEGIN

    IF TG_OP = 'DELETE' THEN
    modified_row := OLD;
ELSE
    modified_row := NEW;
END IF;
raise notice 'opearation: %, co: %', TG_OP, modified_row.co;
    RETURN modified_row;
END;
$$;

CREATE TRIGGER trigger_test1
BEFORE DELETE OR INSERT on test1
FOR EACH ROW
EXECUTE PROCEDURE testfunc();

insert into test1 (th, co, ty) values ('t1', 'c1', 'p'), ('t2', 'c2','p'), ('t2', 'c3','p'), ('t2', 'c4','p');
delete from test1 where ctid  in (select ctid from test1 order by co desc);

The delete statement above is the highest voted and accepted answer in the above post. But for me, the output has always been:

NOTICE: opearation: DELETE, co: c1

NOTICE: opearation: DELETE, co: c2

NOTICE: opearation: DELETE, co: c3

NOTICE: opearation: DELETE, co: c4

which indicates that the deletes are not done in desc order of 'co' that I am trying to do. Am I missing something here?

NOTE: forget about the insert order, I am only concerned about the delete order. And I could not ask this question in the above post because I do not have enough reputation to put a comment.

If you use LIMIT 1 in your delete subselect, you will delete the last one first. If you do that in a loop, you will get the order you desire. — hruske
– hruske, Commented Mar 2, 2016 at 22:43
okay, I will try those. But there is no way to sort the deletes then? — ski
– ski, Commented Mar 3, 2016 at 0:15
@ski: Unfortunately not. Even if you find something which seems to work now, the planner might (for no obvious reason) choose to change the order next time you run it. I think a DELETE ... WHERE CURRENT OF <cursor> in a DO block is the best way to do it. — Nick Barnes
– Nick Barnes, Commented Mar 3, 2016 at 4:46
Why are you trying to delete rows in a certain order? What is the underlying problem that you are trying to solve with that? — user330315
– user330315, Commented Mar 3, 2016 at 6:56

pumbo · Accepted Answer · 2016-03-03 05:35:37Z

2

The only way I can think of is delete in a loop. Here's an example using a cursor:

DO $$
DECLARE 
    test1_row test1%ROWTYPE;
    DECLARE cur CURSOR FOR SELECT * FROM test1 ORDER BY co DESC FOR UPDATE;

BEGIN
    FOR test1_row IN cur 
    LOOP
        DELETE FROM test1 WHERE CURRENT OF cur;
    END LOOP;
END $$

An example without a cursor:

DO $$
DECLARE 
    test1_row test1%ROWTYPE;    
BEGIN
    FOR test1_row IN SELECT * FROM test1 ORDER BY co DESC
    LOOP
        DELETE FROM test1 WHERE th = test1_row.th AND co = test1_row.co AND ty = test1_row.ty;
    END LOOP;
END $$

Note: the WHERE condition in DELETE statement above is not reliable because test1 table does not have a primary/unique key. In other cases, you could just use: WHERE id = row.id

edited Mar 3, 2016 at 5:35

answered Mar 3, 2016 at 5:29

pumbo

3,9162 gold badges29 silver badges29 bronze badges

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

Collectives™ on Stack Overflow

Delete with sorting in PostgreSQL

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related