How to delete only one row from non unique set of rows in Postgres?

August 9, 2022

I have this table

create table order_details(
    id_of_product integer,
    id_of_person text
)

both fields are not unique. How to delete one row from the table with specified id_of_product and id_of_person

for example i need to delete one row with id of 6 P.S person is the same for this set of rows

sample data

>Solution :

The question is: don’t you have other columns that you can use to further filtering or ordering uniquely the results?
The resultset could come back in different orders if more rows are equal.

If instead you have a way to determine uniquely the order, you can achieve it. For example, creating the table with the additional change_dt column and inserting some data

create table order_details(
    id_of_product integer,
    id_of_person text,
    change_dt timestamp default now()
);

insert into order_details values (6, 'A');
insert into order_details values (6, 'A');
insert into order_details values (6, 'A');
insert into order_details values (6, 'A');
insert into order_details values (7, 'A');

You can see that the columns can now be ordered uniquely

select *, 
    rank() over (partition by id_of_product, id_of_person order by change_dt) rnk 
from order_details;

Result

 id_of_product | id_of_person |         change_dt          | rnk
---------------+--------------+----------------------------+-----
             6 | A            | 2022-08-09 09:33:39.627656 |   1
             6 | A            | 2022-08-09 09:33:39.870108 |   2
             6 | A            | 2022-08-09 09:33:39.899382 |   3
             6 | A            | 2022-08-09 09:33:39.919873 |   4
             7 | A            | 2022-08-09 09:33:40.013748 |   1
(5 rows)

you can now delete, for example, the "2nd oldest" 6-A row (change_dt = 2022-08-09 09:33:39.870108) with:

delete from order_details where
(id_of_product, id_of_person, change_dt) = 
    (
    select id_of_product, id_of_person, change_dt
    from
        (select *, 
            rank() over (partition by id_of_product, id_of_person order by change_dt) rnk 
        from order_details 
        where id_of_product=6 and id_of_person='A') ranking
    where rnk=2
    )
;

Result

 id_of_product | id_of_person |         change_dt          |
---------------+--------------+----------------------------+
             6 | A            | 2022-08-09 09:33:39.627656 |
             6 | A            | 2022-08-09 09:33:39.899382 |
             6 | A            | 2022-08-09 09:33:39.919873 |
             7 | A            | 2022-08-09 09:33:40.013748 |