Remove consecutive duplicate rows in Postgresql

Question

I have a table having 3 columns (id, amount, time). The table is arranged in ascending order based on id. I want to remove all consecutive rows that have same value in the amount column except the first one.

eg:

id: 12,15,16,17,19,22
amount: 555,546,546,53,53,500
time:

Answer:

id:12,15,17,22    amount: 555,546,53,500    time:

The time column is same for consecutive rows whenever the consecutive rows have same values in the amount column. I searched on SO but could not find a similar question

Refer this link : stackoverflow.com/questions/18439054/… , answer available in SO. — DebashisDeb
– DebashisDeb, Commented Mar 2, 2018 at 21:10
Duplicate of How to delete duplicate entries. Also, there's no such thing as "consecutive rows" in SQL, not like you're thinking. You can explicitly ask the database to ORDER BY <column> rows, but if you leave that clause out, the order you see is undefined. — Clockwork-Muse
– Clockwork-Muse, Commented Mar 5, 2018 at 5:34

Gordon Linoff · Accepted Answer · 2018-03-02 21:04:46Z

7

You can use lag():

select t.*
from (select t.*, lag(amount) over (order by id) as prev_amount
      from t
     ) t
where prev_amount is distinct from amount;

answered Mar 2, 2018 at 21:04

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Aman Over a year ago

Sorry I posted while still typing the question

Boy · Accepted Answer · 2018-03-05 05:28:31Z

1

This might not be an effective method but it works faster if you have large table (>2 million rows).

Copy the table as csv format with DISTINCT on amount column:

\COPY (SELECT DISTINCT ON (amount) * FROM Tablename) to '/home/user/table.csv' with csv delimiter ',';
Truncate the previous table :

TRUNCATE TABLE Tablename;
Copy back the dumped table :

\COPY Tablename from '/home/user/table.csv' with csv delimiter ',';

I've tried deleting duplicates but it took me a day for the query to complete. This method serves me well.

answered Mar 5, 2018 at 5:28

Boy

261 bronze badge

Collectives™ on Stack Overflow

Remove consecutive duplicate rows in Postgresql

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related