Postgres: delete rows order by without primary key

Question

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

For example:

DELETE 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 1

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.