91DarioDev 91DarioDev - 3 years ago 186
SQL Question

Postgres: delete rows order by without primary key

I would like to delete most recent insert up to ‘n’ rows.

For example:

DELET FROM users
WHERE user_id = %s AND group_id = %s
ORDER BY message_date DESC


I understood it’s a syntax error and searching on the web I found many stackoverflow answers telling to use a form like

DELETE FROM users WHERE id IN (SELECT id ....)


Unfortunately I don’t have a primary key on that table, they are just inserts without an auto increment.

How can I do it?

Maybe I should something like

WITH t AS (
SELECT * FROM users
WHERE user_id = %s AND group_id = %s
ORDER BY message_date DESC
)
DELETE FROM t


???

Answer Source

You can adapt the code you found using ids:

DELETE users u
    WHERE u.user_id = %s AND u.group_id = %s AND
          u.message_date IN (SELECT u2.message_date
                             FROM users u2
                             WHERE u2.user_id = u.user_id AND
                                   u2.group_id = u.group_id
                             ORDER BY u2.message_date DESC
                             LIMIT <n>
                            );

I hope you have learned from this how useful serial primary keys can be in a database.

Note: This can delete more than rows, if there are ties in the database.

EDIT:

Let me add, I would be more inclined to approach this as:

DELETE users u
    FROM (SELECT u2.*,
                 ROW_NUMBER() OVER (PARTITION BY user_id, group_id ORDER BY message_date DESC) as seqnum
          FROM users u2
         ) u2
         ON u2.user_id = u.user_id AND u2.group_id = u.group_id AND
            u2.message_date = u.message_date
     WHERE u.user_id = %s AND u.group_id = %s AND
           seqnum <= <n>;

This ensures that exactly rows are deleted, even with ties.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download