PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – DELETE unnecessary information

I have a table that consists information about status of each customer every day. However, due to space saving, I would like to keep that information only if something has changed. More specifically:

ID Customer ID Date Status
1 1 2024-02-01 ACTIVATED
2 1 2024-02-02 ACTIVATED
3 1 2024-02-03 ACTIVATED
4 1 2024-02-04 DEACTIVATED
5 2 2024-02-01 ACTIVATED
6 2 2024-02-02 ACTIVATED
7 3 2024-02-03 ACTIVATED
8 4 2024-02-04 ACTIVATED
9 5 2024-02-05 DEACTIVATED

How do I remove (DELETE) rows 2,3,6,7,8 and keep 1,4,5,9 with SQL?

Tried ranking, but didn’t get there.

Tags:

3

Answers


  1. 0 
    with myData as
(
    select *, 
       lag(status) over (order by id) as prevStatus 
    from myTable
)
select id, CustomerId, date, status
from myData 
where prevStatus is null or prevStatus <> status;

    Based on this your delete would be like:

    with myData as
(
    select *, 
       lag(status) over (order by id) as prevStatus 
    from myTable
)
delete from myTable
  where exists (select * from myData
where myTable.id = myData.Id and 
      myData.prevStatus = myData.status);

    DBFiddle demo

    Login or Signup to reply.
  2. 0

    When I do read your question, I think you want to keep all rows where R=1

    SELECT *,
     row_number() OVER (partition by customer_id, status order by date asc) as R
FROM mytable
ORDER by id;

    The DELETE statement could be:

    DELETE 
FROM mytable
WHERE id in (
  SELECT id
  FROM (
     SELECT *,
        row_number() OVER (partition by customer_id, status order by date asc) as R
     FROM mytable
  ) x 
  where x.R>1
)

    see: DBFIDDLE

    Login or Signup to reply.
  3. 0

    Is there a logic behind which row to delete?
    To answer the question:
    "How do I remove (DELETE) rows 2,3,6,7,8 and keep 1,4,5,9 with SQL?"

    DELETE FROM tbl WHERE ID In(2, 3, 6, 7, 8);

    That is probably not what you were asking. We could only gues what you realy need to do. Here is one gues (though not deleting all IDs as in question) – maybe you want to delete multiple activations of customer id and keep just the first one. Below is code deleting IDs 2, 3, 6 as those customers were already activated.

    --  delete multiple activations of customer id
DELETE FROM tbl 
Where ID IN ( SELECT t.ID 
              FROM ( Select ID, CUST_ID, A_DATE, STATUS, 
                            Count(A_DATE) Over(Partition By CUST_ID Order By A_DATE
                                 Rows Between Unbounded Preceding And Current Row) as RN
                     From tbl ) t
              WHERE RN > 1 And STATUS = 'ACTIVATED'
            )
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search