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

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

CodePudding user response：

You can use system column ctid to specify particular row, even if it has no unique values.

WITH u AS 
(
    SELECT distinct on (id_of_product, id_of_person) id_of_product, id_of_person, ctid 
    FROM order_details
)
DELETE FROM order_details 
WHERE id_of_product=6 AND id_of_person='ey...' AND ctid IN (SELECT ctid FROM u)

CodePudding user response：

you can use the internal column ctid to pick a unique row to be deleted:

delete from order_details
where id_of_product = 6
  and ctid = (select min(ctid) 
              from order_details 
              where id_of_product = 6);

CodePudding user response：

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 |

Edit: as other suggest, you can also use the ctid column, but I believe you would want to delete a "precise" row based on other column values